ClickHouse更新JSON字段报错，咨询正确实现方法

问题重现 #

尝试更新mynamespace.mytable表中JSON列message的source字段，执行以下SQL时触发语法错误：

ALTER TABLE mynamespace.mytable
UPDATE message.source= NULL
WHERE web_id='xxx' and 
equals(message.source,'test')

错误提示 #

Code: 62. DB::Exception: Syntax error: failed at position 76 ('.') (line 2, col 15): .source= NULL
WHERE web_id='xxx' and
equals(message.source,'test'). Expected one of: token, Equals. (SYNTAX_ERROR) (version 22.6.7.7 (official build))

目标表结构 #

CREATE TABLE mynamespace.mytable
(
    `web_id` String,
    timestamp DateTime,
    message JSON
)
ENGINE = MergeTree
ORDER BY (wi,uid)

正确实现方式 #

ClickHouse不支持直接通过.访问JSON子属性并赋值的更新语法，必须借助JSONSet函数修改JSON对象，同时WHERE条件中需用JSON提取函数获取子属性值。

修正后的更新SQL

ALTER TABLE mynamespace.mytable
UPDATE message = JSONSet(message, '$.source', NULL)
WHERE web_id = 'xxx' AND JSONExtractString(message, '$.source') = 'test'

核心要点

• 修改JSON字段：使用JSONSet函数，语法为JSONSet(json_obj, json_path, new_value)。其中$.source是标准JSON路径，用于定位message内的source字段，将其值设为NULL。
• 条件中提取子属性：通过JSONExtractString（对应字符串类型子属性）提取message中的source值，不能直接用message.source的方式访问。
• 版本适配：该写法兼容ClickHouse 22.x及以上版本（你的22.6.7.7版本完全适用）。

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