求助：Snowflake Lateral Flatten解析JSON无法得到预期结果

源数据 #

待解析的Variant类型JSON数据：

{
  "area": {
    "id": "TEST1",
    "value": "TEST1"
  },
  "testcom": {
    "conditions": [
      {
        "assignedto": {
          "workphone": null
        },
        "isClause": false
      }
    ],   
    "iscritical": true,
    "value": "Conditon"
  }
}

期望输出 #

想要得到的查询结果：

questions answer      isClause
testcom   conditions  FALSE

错误SQL及问题分析 #

你执行的错误SQL语句：

select 
lvl_1.key,
lvl_1.value 
from 
    (
    select 
    parse_json(col1) as src 
    from 
    table1
    )xyz 
,lateral flatten(input => xyz.src) lvl_1,
,lateral flatten(input => lvl_1.value) lvl_2,
,lateral flatten(input => lvl_2.value:isClause) lvl_3

问题点 #

1. 语法错误：lateral flatten语句之间多了逗号（,,lateral flatten），会直接导致SQL执行失败。
2. Flatten层级逻辑错误：
   • 第二层Flatten直接展开lvl_1.value，会把testcom下的conditions（数组）、iscritical（布尔值）、value（字符串）都展开，非数组类型的字段展开无意义，还会干扰结果。
   • 第三层Flattenlvl_2.value:isClause完全错误，isClause是单个布尔值，不是可展开的数组或对象，Flatten这个不会得到有效数据。
3. 未筛选目标字段：没有过滤出testcom和conditions相关的条目，也没有正确提取isClause的值，更没有按照期望输出定义列名。

正确SQL写法 #

方法一：通过Flatten逐层解析 #

SELECT
    'testcom' AS questions,
    'conditions' AS answer,
    lvl_3.value::BOOLEAN AS isClause
FROM (
    SELECT parse_json(col1) AS src FROM table1
) xyz
-- 展开根节点，筛选出testcom条目
LATERAL FLATTEN(input => xyz.src) lvl_1
WHERE lvl_1.key = 'testcom'
-- 展开testcom下的conditions数组
LATERAL FLATTEN(input => lvl_1.value:conditions) lvl_2
-- 提取数组元素中的isClause值
LATERAL FLATTEN(input => lvl_2.value:isClause) lvl_3

方法二：直接路径提取（更简洁） #

如果conditions数组固定只有一个元素，可以直接通过JSON路径提取，无需多次Flatten：

SELECT
    'testcom' AS questions,
    'conditions' AS answer,
    src:testcom.conditions[0].isClause::BOOLEAN AS isClause
FROM (
    SELECT parse_json(col1) AS src FROM table1
) xyz

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