在Snowflake中用递归扁平化提取嵌套JSON的CLIENT_FID值

示例JSON #

{
  "rules": [
    {
      "target": "USER_ID",
      "values": ["123", "456"]
    },
    {
      "target": "GROUP",
      "rules": [
        {
          "target": "CLIENT_FID",
          "values": ["fid_001", "fid_002"]
        },
        {
          "target": "ROLE",
          "values": ["admin"]
        }
      ]
    },
    {
      "rules": [
        {
          "rules": [
            {
              "target": "CLIENT_FID",
              "values": ["fid_003"]
            }
          ]
        }
      ]
    }
  ]
}

现有繁琐SQL示例 #

WITH flattened AS (
  SELECT 
    value::JSON AS rule_obj
  FROM 
    your_table,
    LATERAL FLATTEN(input => your_json_column, recursive => TRUE)
  WHERE 
    value::JSON:target IS NOT NULL
)
SELECT 
  ARRAY_CAT_AGG(rule_obj:values) AS client_fid_values
FROM 
  flattened
WHERE 
  rule_obj:target = 'CLIENT_FID';

优化方案 #

方案1：用JSON_PATH_QUERY_ARRAY直接定位 #

Snowflake支持JSON路径表达式，通过递归路径遍历所有嵌套的rules节点，结合过滤条件直接提取目标values数组，无需展开全量数据：

SELECT 
  JSON_PATH_QUERY_ARRAY(your_json_column, '$..rules[*]?(@.target == "CLIENT_FID").values') AS client_fid_values
FROM 
  your_table;

• 路径说明：
  • $..rules[*]：递归匹配所有层级下的rules数组元素
  • ?(@.target == "CLIENT_FID")：过滤target等于CLIENT_FID的节点
  • .values：提取匹配节点的values数组

方案2：简化递归Flatten逻辑 #

如果偏好使用Flatten，可以直接在Flatten后过滤聚合，省去多余CTE：

SELECT 
  ARRAY_CAT_AGG(value:values) AS client_fid_values
FROM 
  your_table,
  LATERAL FLATTEN(input => your_json_column, recursive => TRUE)
WHERE 
  value:target = 'CLIENT_FID'
  AND value:values IS NOT NULL;

预期输出 #

两种方案均会返回合并后的目标数组：

["fid_001", "fid_002", "fid_003"]

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