从嵌套JSON向ClickHouse插入数据失败的解决方法咨询

问题场景 #

S3存储的JSON包含嵌套结构，示例如下：

{
  "testing_field": "tambahan",
  "test_update_2": {
    "code_1": "5B72F80DA8",
    "path_2": "xxx",
    "filename_2": "xxx.pdf",
    "qrcode_2": "xxx"
  }
}

目标表sidebar_staging的列名用下划线替代了JSON中的点分隔（如test_update_2_filename_2对应嵌套字段test_update_2.filename_2），但直接用点语法提取字段并插入时失败，别名映射也未生效，仅非嵌套字段可正常插入。

解决方案 #

1. 修正语法错误 #

首先检查INSERT语句中的S3函数参数，确保参数间用逗号分隔。你提供的插入语句中存在参数缺失逗号的问题（'xxx' 'xxx'），这会直接导致执行失败，修正后应为：

FROM s3(
    'xxx/sample_update_field.json',
    'xxx',
    'xxx'
)

2. 开启嵌套JSON解析设置 #

ClickHouse默认可能未开启嵌套JSON解析，需在查询中添加SETTINGS input_format_json_nested = 1，让引擎正确识别JSON中的嵌套结构，此时点语法提取字段会生效。

3. 正确映射字段与列名 #

方式一：直接提取+别名映射

通过点语法提取嵌套字段，用别名对应目标表的下划线列名，配合嵌套解析设置完成插入：

INSERT INTO example_db.sidebar_staging (
    testing_field,
    test_update_2_code_1,
    test_update_2_filename_2,
    test_update_2_path_2,
    test_update_2_qrcode_2
)
SELECT
    testing_field,
    test_update_2.code_1 AS test_update_2_code_1,
    test_update_2.filename_2 AS test_update_2_filename_2,
    test_update_2.path_2 AS test_update_2_path_2,
    test_update_2.qrcode_2 AS test_update_2_qrcode_2
FROM s3(
    'xxx/sample_update_field.json',
    'xxx',
    'xxx'
)
SETTINGS input_format_json_nested = 1

方式二：使用JSONExtract函数提取

如果不想依赖全局设置，可使用JSONExtractString函数逐层提取嵌套字段，无需开启额外设置：

INSERT INTO example_db.sidebar_staging (test_update_2_filename_2)
SELECT JSONExtractString(raw, 'test_update_2', 'filename_2') AS test_update_2_filename_2
FROM s3(
    'xxx/sample_update_field.json',
    'xxx',
    'xxx'
)
FORMAT JSONEachRow

原理说明 #

• input_format_json_nested = 1：让ClickHouse将JSON中的嵌套对象解析为结构体类型，支持用点语法访问嵌套字段。
• 语法错误（参数缺失逗号）是常见的隐性失败原因，需优先排查。
• 别名映射需确保SELECT语句的别名与目标表列名完全一致，配合正确的字段提取逻辑即可完成插入。

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