Hi need to extract data from an array of array, Im using Athena
create external table test
(
customer string
)
Location 'something-something'
The single row of this table is,
select * from customer limit 1
{ "ID": "XXXX", "USerDate": { "items": [{ "Name": "Nir", "CLG": "NPT", "Place": "CBE", "Any Group": {}, "Interest": { "items": [{ "Games": "Cricket", "Music": "AR" }] }, "Others": {} }] } }
I need to extract the row as like
| ID | Name | Place | Games| Music |
|-----|---------|----------|----------|-----------|
select json_extract_scalar(customer,'$.ID') as ID
,json_extract_scalar(i1.item,'$.Name') as Name
,json_extract_scalar(i1.item,'$.Place') as Place
,json_extract_scalar(i2.item,'$.Games') as Games
,json_extract_scalar(i2.item,'$.Music') as Music
from test
cross join unnest (cast(json_extract(customer,'$.USerDate.items')
as array(json))) as i1 (item)
cross join unnest (cast(json_extract(i1.item,'$.Interest.items')
as array(json))) as i2 (item)
;
ID | Name | Place | Games | Music
------+------+-------+---------+-------
XXXX | Nir | CBE | Cricket | AR
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments