Re: Multiple Aggregations Order

I'm aiming to serialize some graph data into a JSON format, and some of the data needed for the serialization is in the relation tables, like, "this node connects to this other node in this way". These are served to IOT devices and the data changes a lot, so there's a ton of requests and caching is not that efficient. Due to that, it would be ideal if I could fetch everything in a single query, with the data needed aggregated in arrays, so that I can denormalize them later in code (eg.: zip(data['child_id_set'], data['child_kind_set']) in python).WITH RECURSIVE tree(tree_id, tree_path) AS (
    SELECT "conn"."child_id",
           ARRAY["conn"."parent_id"]::integer[]
      FROM "conn"
     WHERE "parent_id" IN (643) -- Starting point of graph
     UNION
    SELECT DISTINCT ON ("conn"."child_id")
           "conn"."child_id",
           "tree"."tree_path" || "conn"."parent_id"
      FROM "tree", "conn"
     WHERE "conn"."parent_id" = "tree"."tree_id"
       AND NOT "conn"."child_id" = ANY("tree"."tree_path")
) SELECT "tb".*,
         array_length("tree"."tree_path", 1) AS "depth",
         array_agg("conn"."child_id" ORDER BY ("conn"."order", "conn"."kind")) FILTER (WHERE "conn"."child_id" IS NOT NULL) AS "child_id_set",
         array_agg("conn"."kind" ORDER BY ("conn"."order", "conn"."kind")) FILTER (WHERE "conn"."child_id" IS NOT NULL) AS "child_kind_set",
         array_agg("conn"."restrictions" ORDER BY ("conn"."order", "conn"."kind")) FILTER (WHERE "conn"."child_id" IS NOT NULL) AS "child_restrictions_set",
         array_agg("conn"."meta" ORDER BY ("conn"."order", "conn"."kind")) FILTER (WHERE "conn"."child_id" IS NOT NULL) AS "child_meta_set"
    FROM "tb"
    LEFT OUTER JOIN "conn"
      ON "tb"."id" = "conn"."parent_id"
   INNER JOIN (SELECT DISTINCT ON ("tree_id") * FROM "tree") AS "tree"
      ON "tree"."tree_id" = "tb"."id"
GROUP BY "tb"."id", "tree"."tree_path";