I have this query in postgresql:
<code>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;
</code>
<code>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;
</code>
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:
<code>transactions
id int
hash var
id | hash
------------
1 | h1
2 | h2
3 | h3
</code>
<code>transactions
id int
hash var
id | hash
------------
1 | h1
2 | h2
3 | h3
</code>
transactions
id int
hash var
id | hash
------------
1 | h1
2 | h2
3 | h3
<code>logs
transaction_id int
field1 var
value var
transaction_id | field1 | value
-------------------------------
1 | f1 | v1
1 | f2 | v2
2 | f3 | v3
3 | f4 | v4
</code>
<code>logs
transaction_id int
field1 var
value var
transaction_id | field1 | value
-------------------------------
1 | f1 | v1
1 | f2 | v2
2 | f3 | v3
3 | f4 | v4
</code>
logs
transaction_id int
field1 var
value var
transaction_id | field1 | value
-------------------------------
1 | f1 | v1
1 | f2 | v2
2 | f3 | v3
3 | f4 | v4
<code>tags
transaction_id int
tag1 var
value var
transaction_id | tag1 | value
-------------------------------
1 | t1 | v1
2 | t2 | v2
2 | t3 | v3
</code>
<code>tags
transaction_id int
tag1 var
value var
transaction_id | tag1 | value
-------------------------------
1 | t1 | v1
2 | t2 | v2
2 | t3 | v3
</code>
tags
transaction_id int
tag1 var
value var
transaction_id | tag1 | value
-------------------------------
1 | t1 | v1
2 | t2 | v2
2 | t3 | v3
I need result:
<code>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'}] | []
</code>
<code>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'}] | []
</code>
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