SELECT a.id,
           SELECT c.id FROM table_c c
               JOIN table_d d ON d.id = c.id
               JOIN table_e e ON e.id = d.id
                WHERE a_id = a.id AND user_id = a.user_id
           )) AS first_subquery,
       (SELECT json_agg(json_build_object(
               'id', b.id,
               'first_field', b.first_field,
           )) FROM table_b b where  b.id = a.id) AS second_subquery
FROM table_a a;

It's hard to explain the problem without copy-pasting a complete query. This is almost the original query, except that names have been replaced.

Here are the use cases:

  • For first_subquery, a JOIN could also work but it just felt more intuitive to write this dependent subquery. The dataset size is not large so performance might not become a bottleneck regardless of executing this subquery for each row of the main query

  • For second_subquery, however, I am not sure I know a more convenient way to achieve the goal. And the goal is to collect each row from table_a along with a jsonb array of objects with matching rows from table_b. For simplicity, let's say you want to get football teams with the squads included as a list of objects for each.

I hope that this sample and my description are enough to understand what I would like to achieve. My impression is that first_subquery might get too inefficient as the dataset increases. The same could be true for second_subquery but I really like the readability and honestly speaking I haven't managed to come up with any alternative.

What are your suggestions?

  • 1
    Co-related sub-queries are usually substantially slower as a corresponding derived table. But your sub-queries can be re-written to derived tables (using GROUP BY)
    – user1822
    Sep 23, 2022 at 18:44
  • @a_horse_with_no_name, by derived tables you mean moving those subqueries to the FROM clause or transforming them to JOINS and then grouping by a.id?
    – Don Draper
    Sep 23, 2022 at 18:49
  • I am now trying to rewrite it as two JOINS with non-correlated subqueries but that predictably results in duplicate jsonb objects due to the number of different resulting tuples
    – Don Draper
    Sep 23, 2022 at 19:23
  • 2
    Then you are not grouping in the derived tables. Something like: pastebin.com/U2uusZK6
    – user1822
    Sep 23, 2022 at 19:26
  • @a_horse_with_no_name, oh, right, thank you so much. Joining them this way does indeed generate the desired result
    – Don Draper
    Sep 23, 2022 at 19:42


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.