Postgres: hierarchical one to many jsonb aggregation

Question

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!

Note that your queries relate to a parent_table, while your sample data only show child_table. — GMB
– GMB, Commented Jun 4, 2020 at 23:45
I wanted to keep the parent table concise assuming that it only has parent_id, but Im happy to add parent table — socket_var
– socket_var, Commented Jun 4, 2020 at 23:53

GMB · Accepted Answer · 2020-06-04 23:44:26Z

1

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"}]

answered Jun 4, 2020 at 23:44

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

socket_var Over a year ago

I had a feeling I was overcomplicating. I guess I just needed a fresh set of eyes from you, tysm!

Collectives™ on Stack Overflow

Postgres: hierarchical one to many jsonb aggregation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related