如何在AggregateFunction中使用JSON类型？类型转换报错求助

错误信息 #

DB::Exception: Conversion from AggregateFunction(argMax, Tuple(_dummy UInt8), DateTime) to AggregateFunction(argMax, Object('json'), DateTime) is not supported: ... (CANNOT_CONVERT_TYPE) (version 23.4.1.1943 (official build))

问题场景 #

现有存储原始数据的ReplacingMergeTree表raw，包含JSON类型的value字段，希望通过物化视图将每个user_id对应的最新value同步到AggregatingMergeTree表state，但触发上述类型转换错误。核心原因是JSON类型底层为可变Tuple结构，而聚合函数要求固定类型签名。

相关表及物化视图原定义：

原始表raw #

CREATE TABLE raw (
  user_id           String,
  created_at        DateTime,
  value             JSON
)
ENGINE = ReplacingMergeTree()
PRIMARY KEY (created_at, user_id)

目标聚合表state（原定义） #

CREATE TABLE state (
  user_id           String,
  _value            AggregateFunction(argMax, JSON, DateTime)
)
ENGINE = AggregatingMergeTree()
PRIMARY KEY (user_id)

物化视图state_mv（原定义） #

CREATE MATERIALIZED VIEW state_mv TO state AS
  SELECT
    user_id,
    argMaxState(raw.value, raw.created_at) AS _value
  FROM raw
  GROUP BY user_id

可行解决方法 #

方法1：JSON转String存储，查询时解析 #

利用字符串类型的兼容性，先将JSON序列化为String存入聚合表，查询时再解析回JSON：

修改目标聚合表state

CREATE TABLE state (
  user_id           String,
  _value            AggregateFunction(argMax, String, DateTime)
)
ENGINE = AggregatingMergeTree()
PRIMARY KEY (user_id)

修改物化视图state_mv

CREATE MATERIALIZED VIEW state_mv TO state AS
  SELECT
    user_id,
    argMaxState(toString(raw.value), raw.created_at) AS _value
  FROM raw
  GROUP BY user_id

查询最新JSON值

SELECT
  user_id,
  JSONParse(argMaxMerge(_value)) AS value
FROM state
GROUP BY user_id

方法2：固定JSON结构，转为Tuple/结构体 #

若value的JSON结构固定，可提前解析为Tuple或自定义结构体，聚合函数支持固定类型的聚合操作：

假设JSON结构为{"name": "xxx", "age": 18}，修改目标聚合表

CREATE TABLE state (
  user_id           String,
  _value            AggregateFunction(argMax, Tuple(name String, age UInt8), DateTime)
)
ENGINE = AggregatingMergeTree()
PRIMARY KEY (user_id)

修改物化视图

CREATE MATERIALIZED VIEW state_mv TO state AS
  SELECT
    user_id,
    argMaxState(JSONExtract(raw.value, 'Tuple(name String, age UInt8)'), raw.created_at) AS _value
  FROM raw
  GROUP BY user_id

查询结构化数据

SELECT
  user_id,
  argMaxMerge(_value) AS value
FROM state
GROUP BY user_id

方法3：改用ReplacingMergeTree替代聚合表 #

若仅需保留每个user_id的最新数据，直接用ReplacingMergeTree实现，无需依赖聚合函数：

创建目标表state

CREATE TABLE state (
  user_id           String,
  created_at        DateTime,
  value             JSON
)
ENGINE = ReplacingMergeTree(created_at)
PRIMARY KEY (user_id)
ORDER BY (user_id, created_at)

创建物化视图

CREATE MATERIALIZED VIEW state_mv TO state AS
  SELECT
    user_id,
    created_at,
    value
  FROM raw

ClickHouse执行合并时，会自动保留每个user_id对应最大created_at的记录，效果与argMax一致，同时避开JSON类型的聚合限制。

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