如何解析含嵌套$符号键的JSON？寻求替代json_query的函数方案

针对嵌套带$符号键的JSON，以下几种方案可以更高效地提取数据，替代原有点符号的局限方法：

方案1：使用JSON_PATH_QUERY直接指定完整JSON路径 #

通过JSON路径表达式，用双引号包裹带$的键名，直接定位到嵌套层级的目标值，无需分步调用json_query。

with example as (
  select '{"id1":{"$id2":"12345"},"something":{"$id3":{"$id4":"67890"}}}' as json_col
)
select
  JSON_PATH_QUERY(parse_json(json_col), '$.id1."$id2"') as id2,
  JSON_PATH_QUERY(parse_json(json_col), '$.something."$id3"."$id4"') as id4
from example;

这个方法直接通过标准JSON路径语法访问嵌套的特殊键，支持任意层级的嵌套结构，比分步调用更简洁。

方案2：使用FLATTEN展开所有嵌套层级（提取所有键值对） #

如果需要一次性提取JSON中所有层级的键和值，FLATTEN配合recursive参数可以递归展开整个JSON结构：

with example as (
  select parse_json('{"id1":{"$id2":"12345"},"something":{"$id3":{"$id4":"67890"}}}'::variant) as json_col
)
select
  f.path as full_key_path,
  f.value as key_value
from example,
lateral flatten(input => json_col, recursive => true, path => '$') f;

执行后会输出所有键的完整路径和对应值：

方案3：递归CTE遍历所有嵌套节点（结构化提取） #

如果需要更灵活的结构化输出，比如将所有键值对整理成扁平的行或自定义结构，可以用递归CTE遍历每个节点：

with example as (
  select parse_json('{"id1":{"$id2":"12345"},"something":{"$id3":{"$id4":"67890"}}}'::variant) as json_col
),
recursive_parse as (
  -- 初始层：提取顶级键
  select
    object_keys(json_col) as key_name,
    json_col[key_name] as key_value,
    key_name as path
  from example, lateral flatten(input => object_keys(json_col)) key_name
  union all
  -- 递归层：处理嵌套对象
  select
    object_keys(r.key_value) as nested_key,
    r.key_value[nested_key] as nested_value,
    r.path || '.' || nested_key as full_path
  from recursive_parse r
  where typeof(r.key_value) = 'OBJECT'
)
select
  full_path as key_path,
  nested_value as key_value
from recursive_parse
where typeof(nested_value) != 'OBJECT'; -- 只输出最终值节点

这个方法可以自定义输出格式，适合复杂JSON结构的全量提取需求。

原代码问题说明 #

原代码中错误使用了json_keys，应该直接用parse_json解析JSON为Variant类型。点符号访问带$的键时，嵌套层级会因为语法解析问题失效，而上述方案通过标准JSON路径或递归遍历的方式规避了这个问题。

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