Select a group of multiple children per each member of a selected group of parents (with their own parents as well)

Question

I have the following scheme built using PostgreSQL -

Table A:
    id | name
    1  | a
    2  | b 
Table B:
    id | parent-A-id  | searchValue | dataB
    1  | 1            | val1        | a
    2  | 2            | val2        | b
    3  | 2            | val2        | c
Table C:
    id | parent-A-id  | dataC
    1  | 1            | x
    2  | 2            | y
    3  | 2            | z
Table D:
    id | parent-A-id  | parent-B-id | searchValue-REF 
    1  | 1            | 1           | val1
    2  | 2            | 2           | val2
    2  | 3            | 2           | val2
    2  | 3            | 3           | val2

I am trying to retrieve the following data -

Given N, find n amount of B, its parent A and a subset of children C, using only the items D that comply to a function running on searchByVal-REF, meaning a computed result is calculated (e.g - location), against N value.

An example of an output of data based on N would be -

[
    {
        A.name : "a",
        A.id : 1
        B.id : 1,
        B.dataB : "a",
        [{C.id : 1, C.dataC : "x"}],
        computedVal : someVal
    },
    {
        A.name : "b",
        A.id : 2
        B.id : 2,
        B.dataB : "b",
        [{C.id : 2, C.dataC : "y"}],
        computedVal : someVal
    },
     {
        A.name : "b",
        A.id : 2
        B.id : 3,
        B.dataB : "c",
        [{C.id : 2, C.dataC : "y"}, {C.id : 3, C.dataC : "z"}],
        computedVal : someVal
    }
]

The data is basically rows of Bs & parent A of each B & n children C of each B in a serialized array of rows represented in a single column per row B.

I am not sure if I'm over-complicating it, but at the moment I'm trying to group a set of records C into a single column by turning an array of results into a serialized json. But I've yet to manage to get it work as I'm trying to reference a subquery on an outer query which fails as I think that the query is too nested.

This is where I'm stuck at -

SELECT
  b_items_found.*,
  row_to_json(C_json_arr) as C_list
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          array_agg("parent-C-id") as selected_Cs,
          "parent-B-id",
          computedVal
        FROM
          (
            SELECT
              ComputeA(searchValue-REF) AS computedVal,
              "parent-B-id",
              "parent-C-id"
            FROM
              D
              CROSS JOIN (
                SELECT
                  ComputeC(N)
              ) AS r
            WHERE
              ComputeB(searchValue-REF)
            ORDER BY
              ComputeA(searchValue-REF)
          ) select-b-items
        GROUP BY
          "parent-B-id",
          computedVal
      ) b_and_c
      JOIN B ON b_and_c."parent-B-id" = B.id
      JOIN A ON B."parent-a-id" = A.id
  ) b_items_found,
  (
    SELECT
      array_agg(row_to_json(t)) as m
    from
      (
        SELECT
          *
        from
          C
        WHERE C.id = ANY(b_items_found.selected_Cs)
      ) t
  ) C_json_arr

This currently results in -

There is an entry for table "b-items-found", but it cannot be referenced from this part of the query.

I am wondering if there's a single query solution to this or if it should be separated into several queries (querying & processing server side).

Running PostgreSQL 9.6.8

Answer 1

Although the query really looks overly complicated, your main issue is that you are trying the access the temporary table b_items_found in a query where the optimizer may decide to execute the following query first which is why you cannot reference it (no sibling-references, only children).

To do that, you'd have to use a CTE (Common Table Expression), like so:

WITH b_items_found AS (
    SELECT
      *
    FROM
      (
        SELECT
          array_agg("parent-C-id") as selected_Cs,
          "parent-B-id",
          computedVal
        FROM
          (
            SELECT
              ComputeA(searchValue-REF) AS computedVal,
              "parent-B-id",
              "parent-C-id"
            FROM
              D
              CROSS JOIN (
                SELECT
                  ComputeC(N)
              ) AS r
            WHERE
              ComputeB(searchValue-REF)
            ORDER BY
              ComputeA(searchValue-REF)
          ) select-b-items
        GROUP BY
          "parent-B-id",
          computedVal
      ) b_and_c
      JOIN B ON b_and_c."parent-B-id" = B.id
      JOIN A ON B."parent-a-id" = A.id
  ),
  C_json_arr AS (
    SELECT
      array_agg(row_to_json(t)) as m
    from
      (
        SELECT
          *
        from
          C
        WHERE C.id = ANY(b_items_found.selected_Cs)
      ) t
  )
SELECT
  b_items_found.*,
  row_to_json(C_json_arr) as C_list
FROM b_items_found, C_json_arr

However, that's not tested as your query does not match your table structure.