如何在Snowflake SQL中提取嵌套JSON键？

一、纯SQL递归CTE方案 #

适合轻量嵌套场景，完全用SQL实现，无需额外语言基础。

1. 示例数据准备 #

先创建包含目标嵌套JSON的临时表：

CREATE OR REPLACE TEMP TABLE json_data (
  raw_json VARIANT
);

INSERT INTO json_data VALUES (
  PARSE_JSON('{
    "header": {
      "message_uuid": "abc123",
      "timestamp": 1690000000
    },
    "metadata": {
      "ffprobe": {
        "data": {
          "tags": [
            {"key": "title", "value": "Sample Video"},
            {"key": "artist", "value": "Test Artist"}
          ],
          "duration": 120.5
        }
      }
    }')
);

2. 递归CTE提取全路径键 #

通过递归遍历处理对象和数组嵌套，自动拼接完整键路径：

WITH RECURSIVE json_keys AS (
  -- 初始层：提取顶级键
  SELECT
    OBJECT_KEY_PATH(raw_json, k.key) AS key_value,
    k.key AS full_path,
    raw_json[k.key] AS json_node,
    TYPEOF(raw_json[k.key]) AS node_type
  FROM json_data,
       LATERAL FLATTEN(INPUT => raw_json, MODE => 'PATH') k

  UNION ALL

  -- 递归层：处理嵌套对象/数组
  SELECT
    OBJECT_KEY_PATH(j.json_node, CASE WHEN j.node_type = 'ARRAY' THEN CONCAT('[', f.index, ']') ELSE f.key END) AS key_value,
    CASE
      WHEN j.node_type = 'OBJECT' THEN CONCAT(j.full_path, '.', f.key)
      WHEN j.node_type = 'ARRAY' THEN CONCAT(j.full_path, '[', f.index, ']', CASE WHEN TYPEOF(f.value) = 'OBJECT' THEN '.' ELSE '' END, OBJECT_KEYS(f.value)[0])
      ELSE j.full_path
    END AS full_path,
    CASE
      WHEN j.node_type = 'OBJECT' THEN f.value
      WHEN j.node_type = 'ARRAY' THEN f.value
      ELSE j.json_node
    END AS json_node,
    TYPEOF(CASE
      WHEN j.node_type = 'OBJECT' THEN f.value
      WHEN j.node_type = 'ARRAY' THEN f.value
      ELSE j.json_node
    END) AS node_type
  FROM json_keys j,
       LATERAL FLATTEN(INPUT => j.json_node, MODE => 'PATH') f
  WHERE j.node_type IN ('OBJECT', 'ARRAY')
    AND TYPEOF(f.value) IN ('OBJECT', 'ARRAY', 'STRING', 'NUMBER', 'BOOLEAN')
)

-- 输出最终结果：去重+排序，仅保留叶子节点
SELECT DISTINCT
  full_path,
  key_value
FROM json_keys
WHERE TYPEOF(key_value) NOT IN ('OBJECT', 'ARRAY')
ORDER BY full_path;

效果说明 #

• 非嵌套键（如header.message_uuid）直接保留完整路径
• 数组型嵌套会自动追加索引（如metadata.ffprobe.data.tags[0].key）
• 最终输出所有叶子节点的键路径和对应值

二、Snowflake存储过程方案（适合复杂嵌套场景） #

用JavaScript编写存储过程封装遍历逻辑，团队成员只需调用存储过程，无需理解内部实现。

1. 创建存储过程 #

CREATE OR REPLACE PROCEDURE extract_all_json_keys(table_name VARCHAR, json_column VARCHAR)
RETURNS TABLE(full_path VARCHAR, key_value VARCHAR)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
  var result = [];
  
  // 递归遍历JSON生成完整路径
  function traverseJson(obj, path) {
    if (typeof obj === 'object' && obj !== null) {
      if (Array.isArray(obj)) {
        obj.forEach((item, index) => {
          const newPath = path + '[' + index + ']';
          traverseJson(item, newPath);
        });
      } else {
        Object.keys(obj).forEach(key => {
          const newPath = path ? path + '.' + key : key;
          const value = obj[key];
          if (typeof value !== 'object' || value === null) {
            result.push({full_path: newPath, key_value: String(value)});
          } else {
            traverseJson(value, newPath);
          }
        });
      }
    } else {
      result.push({full_path: path, key_value: String(obj)});
    }
  }
  
  // 查询目标表数据并处理
  var stmt = snowflake.createStatement({
    sqlText: `SELECT ${JSON_COLUMN} FROM ${TABLE_NAME}`
  });
  var rs = stmt.execute();
  
  while (rs.next()) {
    const jsonObj = rs.getColumnValue(1);
    traverseJson(jsonObj, '');
  }
  
  return result;
$$;

2. 调用存储过程 #

只需传入表名和JSON字段名即可：

CALL extract_all_json_keys('JSON_DATA', 'RAW_JSON');

效果说明 #

• 自动适配任意深度的对象/数组嵌套
• 输出格式统一，包含所有键的完整路径和对应值
• 无JS基础的SQL使用者可直接调用，无需修改内部逻辑

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