I have this query in 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;

how to convert it to clickhouse? array_agg I guess it’s groupArray. What about jsonb_build_object?

Example:

transactions
 id int
 hash var

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

logs
 transaction_id int
 field1 var
 value var

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

tags
 transaction_id int
 tag1 var
 value var

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

I need result:

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'}]                                  | []

https://www.db-fiddle.com/f/42X7Dw3k2RbUe2MoJxmQET/6