CODEWITHSUNDEEP
sqlamazon-athenapresto
Manza
Manza

Reputation: 2141

SQL json_array_get - to search an object instead of position

I am trying to get a value from a column in the following query:

SELECT 
    json_array_get(transactions.tags,0) as category,
    transactions.tags, 
    transactions.date as transaction_date,
    transactions.amount,
    CAST(transactions.amount as DECIMAL(20, 2)) as amount_banking,
    transactions.balance,
    opportunity.osc_score_previous_conduct
FROM "decisionengine"."public"."app_banking_transaction" AS "transactions"
LEFT JOIN 
    "decisionengine"."public"."app_banking_transactionbankdetail" AS "transactionsDetails" 
ON 
    "transactionsDetails".id  = "transactions".transactionBankDetail_id 
    
LEFT JOIN 
    "decisionengine"."public"."app_banking_bankingdetail" AS "banking" 
ON 
    "transactionsDetails".bankingDetail_id  = "banking".id 
    
LEFT JOIN 
    "decisionengine"."public"."app_banking_opportunitydetail" AS "opportunity" 
ON 
    "banking".opportunity_id  = "opportunity".id 
LIMIT 5

Query results

On the tags columns sometimes there will be an object with the node category, I want to extract the value of category if this node is present in that JSON.

I can see that json_array_get, allows me to get a position in the array, is there a way using this or how can I amend my query to get the value of category?

EDIT

WITH dataset AS (
    SELECT * FROM (SELECT tags FROM "decisionengine"."public"."app_banking_transaction" LIMIT 10) AS t (json_string)
)

SELECT
    reduce(
        CAST (json_string as ARRAY(MAP(VARCHAR, VARCHAR))),
        map(),
        (s, x) -> map_concat(s,x), 
        s -> s
    )['category']
FROM dataset

I get the error

sql error

Upvotes: 0

Views: 177

Answers (1)

Guru Stron
Guru Stron

Reputation: 142423

To access category values you could use next json path: $.[?(@.category)].category (would return array with values), but json path support in Athena is not that great, so you will need to perform some transformations - first casting json to array of maps, then joining them with reduce to one map and finally extracting category value from the resulting map:

WITH dataset AS (
    SELECT * FROM (VALUES   
       ( JSON '[{"category": "test"},{"prop":2}]'),
       ( JSON '[{"id":"1"}]'),                   
       ( JSON '[{"category": "test1"}, {"prop":2}]')
 ) AS t (json_string))


SELECT
 reduce(
   CAST (json_string as ARRAY(MAP(VARCHAR, VARCHAR))),
   map(),
   (s, x) -> map_concat(s,x), 
   s -> s
 )['category']
FROM dataset

Output:

_col0
test
 
test1

Upvotes: 1

Related Questions