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'}] | []
I'll let you figure out the rest for tags but this should answer the question, groupArray and toJSONString. You need to cast to named tuples.
CREATE TABLE transactions
(
`Id` Int32,
`hash` String
)
ENGINE = Memory
CREATE TABLE logs
(
`transaction_id` Int32,
`field1` String,
`value` String
)
ENGINE = Memory
INSERT INTO transactions VALUES (1, 'h1'), (2, 'h2'), (3, 'h3')
INSERT INTO logs VALUES (1, 'f1', 'v1'), (1, 'f2', 'v2'), (2, 'f3', 'v3'), (3, 'f4', 'v4')
SELECT
Id,
any(hash) AS hash,
toJSONString(groupArray((field1, value)::'Tuple(field1 String, value String)')) AS logs_array
FROM transactions AS t
LEFT JOIN logs AS l ON t.Id = l.transaction_id
GROUP BY Id
Query id: 34c5e09d-1411-497b-903b-13a6ff76f10b
┌─Id─┬─hash─┬─logs_array──────────────────────────────────────────────────┐
1. │ 3 │ h3 │ [{"field1":"f4","value":"v4"}] │
2. │ 2 │ h2 │ [{"field1":"f3","value":"v3"}] │
3. │ 1 │ h1 │ [{"field1":"f1","value":"v1"},{"field1":"f2","value":"v2"}] │
└────┴──────┴─────────────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.002 sec.