hive SQL struct类型和Array<struct>类型转json字符串
数据格式:default_address是struct类型,address是Array<struct>类型:例如:array<struct<id:string,customer_id:string>>目标格式:[{"id": "1111","customer_id": "2222"},{"id": "3333","customer_id": "444
·
数据格式:
default_address是struct类型,address是Array<struct>类型:
例如:array<struct<id:string,customer_id:string>>
目标格式:
[
{
"id": "1111",
"customer_id": "2222"
},
{
"id": "3333",
"customer_id": "4444"
}
]
SQL:
SELECT
concat(
'{',
'"id":"',nvl(default_address.id,''),'",',
'"customer_id":"',nvl(default_address.customer_id,''),'",',
'"first_name":"',nvl(default_address.first_name,''),'",',
'}'
) as default_address,
concat('[',
concat_ws( ','
,collect_list(
concat(
'{',
'"id":"',nvl(temp_addresses.id,''),'",',
'"customer_id":"',nvl(temp_addresses.customer_id,''),'",',
'"first_name":"',nvl(temp_addresses.first_name,''),'",',
'}'
)
))
,']') as addresses
FROM anker_ods.shopify_customers_clean l
ateral view outer explode(addresses) tempTable as temp_addresses
GROUP BY pid,id,email,first_name,last_name,,default_address
更多推荐
已为社区贡献1条内容
所有评论(0)