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)
orderby conn."order")
FROM tb
...
GROUP BY ...