数据格式:
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