João Haas schrieb am 14.01.2020 um 18:26:
I'm working on a query where I need to fetch information from a table
along with some data from a many-to-many connection table in a single
query. My idea is to do an outer join with the connection query and
aggregate the needed data in multiple 'array_agg's, and then handle
this aggregated data later in code.
The issue is, there are a lot of aggs (4 by now, may increase later),
and I need to order these by a 'order' field on the connection table.
I can put an 'ORDER BY "order"' statement inside each 'array_agg',
but I don't think that would be the most efficient way. Doing the
join with a sorted connection table didn't work for me as well,
probably due to other joins on the query. I tried doing some stuff
with subqueries, but all attempts ended up in either failure or
increased query time.
What about aggregating into a single jsonb array?
You lose some of the data type information, but maybe that's OK for the backend
that processes the data.
Something along the lines:
SELECT tb.*,
array_length(tree.tree_path, 1) AS depth,
jsonb_agg(jsonb_build_object('child_id', conn.child_id, 'kind', conn.kind,
'restrictions', conn.restrictions) order by conn."order")
FROM tb
...
GROUP BY ...
