hive SQL struct类型和Array＜struct＞类型转json字符串

遥遥晚风点点

已于 2022-05-28 00:55:30 修改

阅读量3.2k

点赞数 2

分类专栏： Hive SQL 文章标签： sql hive 大数据

于 2022-05-28 00:55:04 首次发布

本文链接：https://blog.csdn.net/mr_ye931/article/details/125013387

版权

Hive 同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

SQL

6 篇文章 0 订阅

订阅专栏

数据格式：

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