如何在ClickHouse中实现PostgreSQL的jsonb_build_object及查询转换

原PostgreSQL查询语句 #

select t.id, t.hash, 
array_agg (distinct jsonb_strip_nulls(jsonb_build_object('field1', l.field1, 'value', l."value"))) logs_array,
array_agg (distinct jsonb_strip_nulls(jsonb_build_object('tag1', tg.tag1, 'value', tg."value"))) tags_array
from transactions t 
left join logs l on t.id = l.transaction_id 
left join tags tg on t.id = tg.transaction_id
where l.field1 = 'f1' and tg.tag1 = 't1'
group by t.id, t.hash
order by t.id;

示例表结构与数据 #

transactions表 #

id | hash 
------------
1  |  h1
2  |  h2
3  |  h3

logs表 #

transaction_id | field1 | value 
-------------------------------
1              | f1     | v1
1              | f2     | v2
2              | f3     | v3
3              | f4     | v4

tags表 #

transaction_id | tag1   | value 
-------------------------------
1              | t1     | v1
2              | t2     | v2
2              | t3     | v3

期望查询结果 #

id | hash | logs_array                                                         | tags_array
------------------------------------------------------------------------------------------
1  | h1   | [{'field1': 'f1', 'value': 'v1'}, {'field1': 'f2', 'value': 'v2'}] | [{'tag1': 't1', 'value': 'v1'}]
2  | h2   | [{'field1': 'f3', 'value': 'v3'}]                                  | [{'tag1': 't2', 'value': 'v2'}, {'tag1': 't3', 'value': 'v3'}]
3  | h3   | [{'field1': 'f4', 'value': 'v4'}]                                  | []

ClickHouse转换方案 #

关键映射说明 #

• PostgreSQL的array_agg(distinct ...)对应ClickHouse的groupArrayDistinct(...)
• PostgreSQL的jsonb_build_object对应ClickHouse的JSON_OBJECT函数，用于构造JSON对象
• jsonb_strip_nulls在ClickHouse中可忽略，因为JSON_OBJECT默认不会包含值为NULL的键（字段本身非NULL时）

注意事项 #

原PostgreSQL查询的where条件存在问题：left join后直接在where中过滤l.field1 = 'f1'和tg.tag1 = 't1'会过滤掉未匹配到日志或标签的行，无法得到期望结果中的id=3数据。需将过滤条件移至join的on子句，或用条件判断保留未匹配行。

最终ClickHouse查询语句 #

SELECT 
    t.id, 
    t.hash, 
    groupArrayDistinct(JSON_OBJECT('field1', l.field1, 'value', l.value)) AS logs_array,
    groupArrayDistinct(if(tg.transaction_id IS NOT NULL, JSON_OBJECT('tag1', tg.tag1, 'value', tg.value), NULL)) AS tags_array
FROM transactions t
LEFT JOIN logs l ON t.id = l.transaction_id
LEFT JOIN tags tg ON t.id = tg.transaction_id
GROUP BY t.id, t.hash
ORDER BY t.id;

若需仅保留logs中field1='f1'的记录且不过滤主表行，可调整为：

SELECT 
    t.id, 
    t.hash, 
    groupArrayDistinct(if(l.field1 = 'f1', JSON_OBJECT('field1', l.field1, 'value', l.value), NULL)) AS logs_array,
    groupArrayDistinct(if(tg.tag1 = 't1', JSON_OBJECT('tag1', tg.tag1, 'value', tg.value), NULL)) AS tags_array
FROM transactions t
LEFT JOIN logs l ON t.id = l.transaction_id
LEFT JOIN tags tg ON t.id = tg.transaction_id
GROUP BY t.id, t.hash
ORDER BY t.id;

结果说明 #

• groupArrayDistinct确保数组中无重复JSON对象
• JSON_OBJECT直接构造符合要求的JSON结构
• if函数处理未匹配行，保证未匹配时数组不会出现无效值，最终空数组自动显示为[]

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