CODEWITHSUNDEEP
sqljsonamazon-web-servicesamazon-athenapresto
OGZ
OGZ

Reputation: 35

AWS Athena Extract Array in Json

I have a json column and inside this json column is an array structure. I couldn't figure out how to get the array there.

I trİed to this

 cast(json_extract(ise, '$.userservice') as varchar)  as ise_user_service

Example Json

ise
        {
       "userpa":"****",
       "userlo":"*****",
       "sessi":"******",
       "cl":{
          "name":"****",
          "id":"*****"
       },
       "usermains":"******",
       "userservice":[
          "1****",
          "23***4**",
          "124****",
          "034****"
       ],
       "usergeoloc":"********",
       "userparty":"*******"
    }

Upvotes: 0

Views: 1050

Answers (1)

Guru Stron
Guru Stron

Reputation: 143243

You can either use json_format to turn it into string or cast to ARRAY(VARCHAR) depending on the usecase:

WITH dataset AS (
    SELECT * FROM (VALUES   
       ( JSON ' {
       "userpa":"****",
       "userlo":"*****",
       "sessi":"******",
       "cl":{
          "name":"****",
          "id":"*****"
       },
       "usermains":"******",
       "userservice":[
          "1****",
          "23***4**",
          "124****",
          "034****"
       ],
       "usergeoloc":"********",
       "userparty":"*******"
    }')
 ) AS t (ise))

 
SELECT json_format(json_extract(ise, '$.userservice')) as ise_string, 
    cast(json_extract(ise, '$.userservice') as ARRAY(VARCHAR)) as ise_array
FROM dataset

Upvotes: 2

Related Questions