Snowflake无聚合行转列实现及JSON数组扁平化优化问询

一、解决无聚合行转列/保留所有行的问题 #

你用PIVOT + MAX/MIN仅返回单行，是因为PIVOT的核心逻辑是按分组键聚合行并转成列，如果未指定分组键，会将所有行合并为一行。若你的目标是保留FLATTEN后的3行完整数据，根本不需要使用PIVOT，直接提取扁平化后的对象字段即可。

假设你的APINVOICELINES是类似[{"id":3,"amount":100},{"id":4,"amount":200},{"id":5,"amount":300}]的数组对象，正确查询如下：

SELECT
    t.id AS parent_id,
    f.value:id::VARCHAR AS line_id,
    f.value:amount::DECIMAL(18,2) AS line_amount
    -- 可继续提取其他需要的字段
FROM your_table t,
LATERAL FLATTEN(input => t.APINVOICELINES) f
WHERE t.id = 29604361;

执行后会直接返回3行，每行对应数组中的一个对象，所有字段展开为列，无需任何聚合操作。

如果你的需求确实是要将多个数组元素转成列但保留多行（比如按数组索引分组），可以借助FLATTEN返回的index字段实现：

WITH flattened_data AS (
    SELECT
        t.id AS parent_id,
        f.value:id::VARCHAR AS line_id,
        f.index AS line_pos
    FROM your_table t,
    LATERAL FLATTEN(input => t.APINVOICELINES) f
    WHERE t.id = 29604361
)
SELECT
    parent_id,
    line_pos,
    line_id
    -- 可添加其他列转换逻辑
FROM flattened_data;

二、处理[{},{},{}]格式数据的最优扁平化方法 #

针对数组嵌套对象的结构，Snowflake有几种高效的扁平化方案：

• 基础扁平化（推荐）：直接用LATERAL FLATTEN展开数组，通过value:字段名::数据类型提取对象字段。这种方法性能最优，适合字段固定的场景：

  SELECT
      t.id,
      f.value:id::INT,
      f.value:description::VARCHAR,
      f.value:quantity::INT
  FROM your_table t,
  LATERAL FLATTEN(input => t.APINVOICELINES) f;
  

• 处理字符串类型数组：如果APINVOICELINES是字符串而非VARIANT类型，先通过PARSE_JSON转换：

  SELECT
      t.id,
      f.value:id::INT
  FROM your_table t,
  LATERAL FLATTEN(input => PARSE_JSON(t.APINVOICELINES)) f;
  

• 递归扁平化多层嵌套：如果数组内的对象还包含子数组，可启用RECURSIVE => TRUE展开所有层级，但注意仅在需要时使用，避免生成过多冗余行：

  SELECT
      t.id,
      f.value:id::INT,
      f.value:sub_items:name::VARCHAR
  FROM your_table t,
  LATERAL FLATTEN(input => t.APINVOICELINES, RECURSIVE => TRUE) f;
  

• 动态提取所有字段：若对象字段不固定，可通过KEYS(f.value)获取所有键，再结合FLATTEN展开键值对（适合临时排查场景，性能略低于固定字段提取）：

  SELECT
      t.id,
      k.value AS field_name,
      f.value[k.value]::VARCHAR AS field_value
  FROM your_table t,
  LATERAL FLATTEN(input => t.APINVOICELINES) f,
  LATERAL FLATTEN(input => KEYS(f.value)) k
  WHERE t.id = 29604361;
  

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