如何修改SQL查询提取JSON中values节点的所有子元素(基于Variant列)
JSON嵌套数组全量提取修正方案
原有查询的问题根源
- 所有JSON路径都硬编码了固定数组下标
[0],本质上只取了每个嵌套数组的第一个元素,没有遍历数组下的全部子节点,因此只能输出单条记录 - 你的JSON结构存在三层嵌套数组:顶层
results数组 → 每个结果节点下的values数组 → 每个values分组下的列对象数组,硬写下标的方式完全无法覆盖所有子元素
修正后可直接运行的查询
通过逐层使用FLATTEN表函数炸开嵌套数组,让数组下的每个子元素单独生成一行记录,同时携带对应的上游公共字段,完整提取所有节点数据:
SELECT record:batchId::string AS batchId, res.value:pageInfo.numberOfPages::int AS NoofPages, res.value:pageInfo.pageNumber::int AS pageNo, res.value:pageInfo.pageSize::int AS PgSz, res.value:requestId::string AS requestId, res.value:showPopup::boolean AS showPopup, col_obj.value:columnId::string AS columnId, col_obj.value:value::string AS val FROM lease src -- 炸开外层results数组,获取每个结果集的公共属性 ,LATERAL FLATTEN(INPUT => src.record:results) res -- 炸开每个结果集下的values数组 ,LATERAL FLATTEN(INPUT => res.value:values) val_group -- 炸开values分组内的列对象数组,让每个列对象生成独立行 ,LATERAL FLATTEN(INPUT => val_group.value) col_obj;
补充注意事项
- 可根据目标表的字段类型,为每个提取的Variant值添加
::类型名的强转逻辑,避免COPY导入时出现类型不匹配 - 如果某层数组可能存在空值,可在对应
FLATTEN函数中添加OUTER => TRUE参数,避免空数组导致整条记录被过滤 - 不需要对固定层级的数组写硬编码下标,通过逐层级炸开即可覆盖所有子元素,不会遗漏数据
内容的提问来源于stack exchange,提问作者vikram kumar
相关产品推荐
相关产品推荐

