CODEWITHSUNDEEP
sqljsonpostgresqlselectgroup-by
socket_var
socket_var

Reputation: 1093

Postgres: hierarchical one to many jsonb aggregation

I have a one-to-many relation between parent and child tables as follows:

Child table:

+----------+-----------+------------+--------------+
| table_id | parent_id | page_index | other_column |
+----------+-----------+------------+--------------+
| t1       | p1        |          1 | foo          |
| t1       | p1        |          2 | bar          |
| t2       | p2        |          1 | baz          |
+----------+-----------+------------+--------------+

I want to get the final result as follows, i.e. group by parent_id and group by page_index:

+-----------+--------------------------------------------+
| parent_id |                   pages                    |
+-----------+--------------------------------------------+
| p1        | [{other_column: foo}, {other_column: bar}] |
| p2        | [{other_column: baz}]                      |
+-----------+--------------------------------------------+

I tried this query:

SELECT parent_table.parent_id, jsonb_agg(child_table.*) as pages
FROM parent_table 
JOIN child_table ON child_table.parent_id = parent_table.parent_id
group by parent_table.parent_id, child_table.page_index

But I got the result containing three rows like:

+-----------+-----------------------+
| parent_id |         pages         |
+-----------+-----------------------+
| p1        | [{other_column: foo}] |
| p1        | [{other_column: bar}] |
| p2        | [{other_column: baz}] |
+-----------+-----------------------+

So I did another aggregation on top of that using a subquery and grouping by parent_id again as follows:

select sub_q.parent_id, jsonb_agg(sub_q.pages) as pages
from (
SELECT parent_table.parent_id, jsonb_agg(child_table.*) as pages
FROM parent_table 
JOIN child_table ON child_table.parent_id = parent_table.parent_id
group by parent_table.parent_id, child_table.page_index
) as sub_q

group by sub_q.parent_id

but I ended up with

+-----------+------------------------------------------------+
| parent_id |                     pages                      |
+-----------+------------------------------------------------+
| p1        | [[{other_column: foo}], [{other_column: bar}]] |
| p2        | [{other_column: baz}]                          |
+-----------+------------------------------------------------+

how do I get the above desired result with each row having a one-dimensional array using the most optimal query?

Would be great if the answer has a db fiddle!

Upvotes: 1

Views: 242

Answers (1)

GMB
GMB

Reputation: 222472

You seem to be overcomplicating this. As far as shown in your sample data, you can get the information you want directly from the child table with simple aggregation:

select
    parent_id
    jsonb_agg(jsonb_build_object('other_column', other_column) order by page_index) pages
from child_table
group by parent_id

Demo on DB Fiddle:

parent_id | pages                                             
:-------- | :-------------------------------------------------
p1        | [{"other_column": "foo"}, {"other_column": "bar"}]
p2        | [{"other_column": "baz"}]

Upvotes: 1

Related Questions