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

遥遥晚风点点

3121人浏览 · 2022-05-28 00:55:04

遥遥晚风点点 · 2022-05-28 00:55:04 发布

数据格式：

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