ClickHouse如何按指定路径提取JSON的指定部分？

问题场景 #

给定如下JSON数据：

{
  "id": 123,
  "name": "John",
  "metadata": {
    "foo": "bar",
    "nums": [1, 2, 3]
  }
}

以及路径列表 ['id', 'metadata.nums', 'not.existing']，需要提取对应路径的内容并保留原JSON的嵌套结构，期望结果为：

{
  "id": 123,
  "metadata": {
    "nums": [1, 2, 3]
  }
}

尝试过JSON_VALUE或JSONExtractKeysAndValuesRaw等内置函数，但前者仅返回值（无键），后者仅支持单层级键提取，无法满足多路径嵌套提取的需求。

解决方案：组合内置函数实现多路径嵌套提取 #

可以通过splitByChar、JSON_VALUE、arrayReduce和JSONMergePatch的组合，实现按指定路径提取并保留嵌套结构的效果，具体SQL示例如下：

WITH
    -- 模拟JSON数据
    raw_json AS (
        SELECT '{
            "id": 123,
            "name": "John",
            "metadata": {
                "foo": "bar",
                "nums": [1, 2, 3]
            }
        }' AS json_str
    ),
    -- 指定需要提取的路径列表
    target_paths AS (SELECT ['id', 'metadata.nums', 'not.existing'] AS path_list)
SELECT
    JSONMergePatch(
        arrayMap(
            path -> (
                WITH
                    -- 将路径按.拆分为层级数组
                    path_parts = splitByChar('.', path),
                    -- 根据路径提取对应值，不存在的路径返回NULL
                    extracted_value = JSON_VALUE(json_str, concat('$."', replaceAll(path, '.', '"."'), '"'))
                SELECT
                    -- 仅处理存在值的路径，避免生成NULL键
                    if(extracted_value IS NOT NULL,
                        -- 从内到外构建嵌套JSON对象
                        arrayReduce(
                            (acc, part) -> JSONBuildObject(part, acc),
                            arrayReverse(path_parts),
                            extracted_value
                        ),
                        '{}' -- 空JSON，合并时不影响结果
                    )
            ),
            path_list
        )
    ) AS final_extracted_json
FROM raw_json, target_paths

逻辑说明 #

• 路径拆分：用splitByChar('.', path)将嵌套路径拆分为层级数组（如metadata.nums拆为['metadata', 'nums']）
• 值提取：通过JSON_VALUE结合拼接的JsonPath表达式，提取对应路径的值，不存在的路径返回NULL
• 嵌套对象构建：用arrayReverse反转层级数组，再通过arrayReduce从内到外递归构建嵌套JSON结构
• 合并JSON：使用JSONMergePatch将所有单个路径生成的JSON片段合并为最终结果，自动忽略空JSON和NULL值

扩展说明 #

• 如果需要处理批量数据，只需将模拟的raw_json替换为实际的表和JSON列即可
• 若不需要过滤不存在的路径，可移除if(extracted_value IS NOT NULL...)判断，此时不存在的路径会以键: NULL的形式出现在结果中

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