Snowflake存储过程JSON过大报错100069的优化方案咨询

最优方案：直接关联查询替代遍历+JSON封装 #

原逻辑里遍历gid再逐个查询、封装JSON的操作完全可以用JOIN查询替代，彻底绕开JSON大小限制，同时性能更优：

SELECT 
    ee.gid,
    ee.event_time,
    ee.event_code,
    ee.event_details -- 替换为你需要的指定列
FROM NApp.CORE.DRIVER d
INNER JOIN napp.core_extensions.eldhos_event ee 
    ON d.gid = ee.gid
WHERE 
    d.你的过滤条件列 = '过滤值' -- 替换为原逻辑中的符合条件
GROUP BY ee.gid, ee.event_time, ee.event_code, ee.event_details -- 按需去重

如果需要在存储过程中返回结果，直接让存储过程返回TABLE类型，无需经过JSON中转：

CREATE OR REPLACE PROCEDURE sp_return_table()
RETURNS TABLE(gid STRING, event_time TIMESTAMP, event_code STRING, event_details STRING)
LANGUAGE SQL
AS
$$
BEGIN
    RETURN SELECT 
        ee.gid,
        ee.event_time,
        ee.event_code,
        ee.event_details
    FROM NApp.CORE.DRIVER d
    INNER JOIN napp.core_extensions.eldhos_event ee 
        ON d.gid = ee.gid
    WHERE 
        d.你的过滤条件列 = '过滤值';
END;
$$;

若必须保留JSON环节：缩小JSON体积的方法 #

• 缩短键名：用极简别名代替长列名，比如把driver_gid改成g，event_occurrence_time改成t，大幅减少JSON字符串长度：

  SELECT OBJECT_CONSTRUCT(
      'g', gid,
      't', event_time,
      'c', event_code
  ) AS event_json
  FROM napp.core_extensions.eldhos_event
  WHERE gid = 当前遍历的gid;
  

• 只保留必要字段：去掉所有不需要返回的列，避免冗余数据占用空间。
• 分批生成JSON：遍历gid时，每处理N个gid就输出一次JSON结果，而非将所有结果合并为一个大JSON。可以用循环计数，达到阈值时调用RESULT_SCAN返回部分结果，最后合并。

替代方案：用ARRAY_AGG分批次返回 #

如果需要返回JSON数组但不想超过大小限制，可以按gid分组生成小JSON数组，再统一返回：

SELECT 
    d.gid,
    ARRAY_AGG(OBJECT_CONSTRUCT(
        't', ee.event_time,
        'c', ee.event_code
    )) AS event_array
FROM NApp.CORE.DRIVER d
JOIN napp.core_extensions.eldhos_event ee ON d.gid = ee.gid
WHERE d.你的过滤条件列 = '过滤值'
GROUP BY d.gid;

之后对每个event_array单独FLATTEN，避免生成超大单JSON。

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