CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athenaprestounnest
Manza
Manza

Reputation: 2141

SQL: Unnest array keeping the same number of rows in aws athena

Base from the following query

SELECT internal_transaction_id, tags FROM "bankstatements"."statements_transactions_sample_data"

I get the below table

+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| internal_transaction_id     | tags                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2173059                     | [{category=null, creditdebit=credit, lendertype=null, pending=null, pre_authorisation=null, thirdparty=null}, {category=null, creditdebit=null, lendertype=null, pending=null, pre_authorisation=null, thirdparty=Internal Transfer Credit}, {category=Internal Transfer, creditdebit=null, lendertype=null, pending=null, pre_authorisation=null, thirdparty=null}]
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2173061                     | [{category=null, creditdebit=credit, lendertype=null, pending=null, pre_authorisation=null, thirdparty=null}, {category=null, creditdebit=null, lendertype=null, pending=null, pre_authorisation=null, thirdparty=UBER}, {category=External Transfer, creditdebit=null, lendertype=null, pending=null, pre_authorisation=null, thirdparty=null}]
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I want to unnest the column "tags" keeping the same number of rows. Currently my query

SELECT 
internal_transaction_id, t.category, t.creditdebit, t.lendertype, t.pending, t.pre_authorisation, t.thirdparty
FROM 
"bankstatements"."statements_transactions_sample_data"
CROSS JOIN UNNEST(tags) AS tag (t)

Have as an out come:

+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| internal_transaction_id     | category            | creditdebit   | lendertype    | pending   | pre_authorisation     | thirdparty                |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| 2173059                     |                     | credit        |               |           |                       |                           |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| 2173059                     |                     |               |               |           |                       | Internal Transfer Credit  |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| 2173059                     | Internal Transfer   |               |               |           |                       |                           |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| 2173061                     |                     | credit        |               |           |                       |                           |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| 2173061                     |                     |               |               |           |                       | UBER                      |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| 2173061                     | External Transfer   |               |               |           |                       |                           |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+

I am wodnering how can I unnest the tags to have only 2 rows like the following:

+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| internal_transaction_id     | category            | creditdebit   | lendertype    | pending   | pre_authorisation     | thirdparty                |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| 2173059                     | Internal Transfer   | credit        |               |           |                       | Internal Transfer Credit  |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+
| 2173061                     | External Transfer   |               |               |           |                       | UBER                      |
+-----------------------------+---------------------+---------------+---------------+-----------+-----------------------+---------------------------+

The tags tag in the JSON looks like this:

[
    {
        "thirdParty": "Other Credits"
    },
    {
        "category": "All Other Credits"
    },
    {
        "creditDebit": "credit"
    }
]

When I define the create:

tags: array<
    struct<
        category: string,
        creditdebit: string,
        lendertype: string,
        pending: string,
        pre_authorisation: string,
        thirdparty: string                                            
    >
>

Upvotes: 2

Views: 767

Answers (1)

leftjoin
leftjoin

Reputation: 38335

aggregate using min or max:

SELECT 
internal_transaction_id, max(t.category) as category, max(t.creditdebit) as creditdebit, max(t.lendertype) as lendertype, max(t.pending) as pending, max(t.pre_authorisation) as pre_authorisation, max(t.thirdparty) as thirdparty
FROM 
"bankstatements"."statements_transactions_sample_data"
CROSS JOIN UNNEST(tags) AS tag (t) 
GROUP BY internal_transaction_id

Upvotes: 2

Related Questions