hive从嵌套数组中提取数据

sd2nnvve 于 2021-06-26 发布在 Hive

关注(0)|答案(4)|浏览(490)

嗨需要从数组中提取数据，我用雅典娜

create external table test
(
customer string
)
Location 'something-something'

这张表的单行是， select * from customer limit 1 ```
{ "ID": "XXXX", "USerDate": { "items": [{ "Name": "Nir", "CLG": "NPT", "Place": "CBE", "Any Group": {}, "Interest": { "items": [{ "Games": "Cricket", "Music": "AR" }] }, "Others": {} }] } }

我需要像这样提取行
|id |名称|地点|游戏|音乐|
|-----|---------|

Hive JSON Arrays amazon-web-services amazon-athena

来源：https://stackoverflow.com/questions/42885695/hive-extract-data-from-nested-array

4条答案

按热度按时间

v64noz0r1#

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

赞(0）回复(0）举报 2021-06-26

cuxqih212#

赞(0）回复(0）举报 2021-06-26

sxissh063#

赞(0）回复(0）举报 2021-06-26

isr3a4wc4#

赞(0）回复(0）举报 2021-06-26

我来回答

hive从嵌套数组中提取数据

4条答案

相关问题

热门标签

最新问答