Snowflake中使用OBJECT_CONSTRUCT规范化数据构造指定JSON的方法

直接用内置的OBJECT_CONSTRUCT搭配数组构造函数即可实现，不需要手动拼接JSON字符串，原生函数会自动处理特殊字符转义、类型匹配问题，稳定性远高于字符串拼接方案。

固定长度数组实现（完全匹配要求结构） #

如果要求输出的Person_Title数组固定包含3个对象，顺序严格为Title_1、Title_2、Title_3，哪怕对应头衔值为空也保留占位，直接用以下写法，假设源表名为staff，姓名字段为user_name：

SELECT
    user_name,
    OBJECT_CONSTRUCT(
        'Person_Title',
        ARRAY_CONSTRUCT(
            OBJECT_CONSTRUCT('Title_1', Title_1),
            OBJECT_CONSTRUCT('Title_2', Title_2),
            OBJECT_CONSTRUCT('Title_3', Title_3)
        )
    ) AS user_info_json
FROM staff;

相关配置说明 #

OBJECT_CONSTRUCT默认会自动丢弃值为NULL的键值对，如果需要保留值为NULL的头衔键，执行以下会话参数配置即可：

ALTER SESSION SET OBJECT_CONSTRUCT_KEEP_NULL = TRUE;

动态过滤空值的可变长度数组实现 #

如果不需要保留空头衔项，数组只保留有实际值的头衔，可以用横向展开+聚合的写法，自动过滤NULL值：

SELECT
    user_name,
    OBJECT_CONSTRUCT(
        'Person_Title',
        ARRAY_AGG(OBJECT_CONSTRUCT(title_key, title_val) HAVING title_val IS NOT NULL)
    ) AS user_info_json
FROM staff,
LATERAL FLATTEN(INPUT => ARRAY_CONSTRUCT(
    OBJECT_CONSTRUCT('title_key', 'Title_1', 'title_val', Title_1),
    OBJECT_CONSTRUCT('title_key', 'Title_2', 'title_val', Title_2),
    OBJECT_CONSTRUCT('title_key', 'Title_3', 'title_val', Title_3)
))
GROUP BY user_name;

两种写法生成的JSON都符合结构化类型校验要求，可以直接存入VARIANT类型字段，或者供下游JSON解析任务直接使用。

内容的提问来源于stack exchange，提问作者Siddharth