Snowflake有序生成JSON及动态传入键值对技术问题求助

一、保持JSON键顺序的解决方案 #

Snowflake的OBJECT_CONSTRUCT_KEEP_NULL函数默认会按键名字典序排序，无法直接保留传入参数的顺序。要实现指定顺序的JSON，需使用ARRAY_TO_OBJECT配合有序的键值对数组：

固定顺序场景示例 #

将每个键值对封装为子数组，通过ARRAY_CONSTRUCT组合成有序数组，最后用ARRAY_TO_OBJECT转换为JSON对象：

SELECT ARRAY_TO_OBJECT(ARRAY_CONSTRUCT(
    ARRAY_CONSTRUCT('B', Beta),
    ARRAY_CONSTRUCT('A', Alpha)
)) AS value
FROM table_test;

执行后生成的JSON会严格遵循数组中的顺序：

{  "B": "Beta",  "A": "Alpha"}

二、从配置表动态生成有序JSON的方案 #

要实现新增键值对无需修改SQL，需先创建一张配置表维护键的顺序、键名和对应原表的列名，再通过动态SQL或存储过程自动拼接生成JSON。

1. 创建配置表 #

配置表需包含排序字段（控制JSON键顺序）、键名、原表列名：

CREATE OR REPLACE TABLE config_table (
    sort_order INT,
    key_name VARCHAR,
    column_name VARCHAR
);

-- 插入初始配置
INSERT INTO config_table VALUES
(1, 'B', 'Beta'),
(2, 'A', 'Alpha'),
(3, 'C', 'Cassandra');

2. 动态SQL实现（无需存储过程） #

通过LISTAGG生成有序的键值对数组元素，再拼接成动态SQL执行：

-- 生成动态数组元素字符串
SET dynamic_elements = (
    SELECT LISTAGG(
        'ARRAY_CONSTRUCT(''' || key_name || ''', ' || column_name || ')',
        ', '
    ) WITHIN GROUP (ORDER BY sort_order)
    FROM config_table
);

-- 执行动态SQL生成有序JSON
EXECUTE IMMEDIATE '
    SELECT ARRAY_TO_OBJECT(ARRAY_CONSTRUCT(' || $dynamic_elements || ')) AS value
    FROM table_test
';

3. 存储过程封装（更易用） #

将逻辑封装为存储过程，调用即可生成动态JSON：

CREATE OR REPLACE PROCEDURE generate_dynamic_json()
RETURNS TABLE(value VARIANT)
LANGUAGE SQL
AS
$$
DECLARE
    dynamic_elements STRING;
    sql_stmt STRING;
BEGIN
    -- 从配置表获取有序的键值对元素
    SELECT LISTAGG(
        'ARRAY_CONSTRUCT(''' || key_name || ''', ' || column_name || ')',
        ', '
    ) WITHIN GROUP (ORDER BY sort_order) INTO dynamic_elements
    FROM config_table;

    -- 拼接执行SQL
    sql_stmt := 'SELECT ARRAY_TO_OBJECT(ARRAY_CONSTRUCT(' || dynamic_elements || ')) AS value FROM table_test';

    -- 返回结果集
    RETURN TABLE(EXECUTE IMMEDIATE sql_stmt);
END;
$$;

-- 调用存储过程
CALL generate_dynamic_json();

说明 #

后续新增键值对时，只需向config_table插入新记录（指定sort_order控制位置），无需修改任何SQL或存储过程，直接执行上述动态SQL或调用存储过程即可生成包含新键的有序JSON。

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