Postgres 3 table join into nested arrays

Question

I'm not sure how to go about this but I'm trying to nest arrays from joining 3 tables.

My tables

projects

                  id                  |         name
--------------------------------------+-----------------------
 c7b0af60-3db4-4f07-a397-ee5d8123c88e | Test

features

                  id                  |           name           |              project_id
--------------------------------------+--------------------------+--------------------------------------
 b5c441f8-79b8-452b-9a42-6c7fc976ca2b | Test Feature             | c7b0af60-3db4-4f07-a397-ee5d8123c88e

feature_items

                  id                  |              feature_id              |      name      | type 
--------------------------------------+--------------------------------------+----------------+------
 d0ffe2d0-526d-4139-8ff3-cdaacde33129 | b5c441f8-79b8-452b-9a42-6c7fc976ca2b | Another test 1 | Bug

With the resulting output coming up to something like this

  {
    "id": "c7b0af60-3db4-4f07-a397-ee5d8123c88e",
    "name": "Test",
    "features": [
        {
            "id": "847b5aef-495a-4ef3-ae39-3abf746e61fd",
            "name": "Test Feature",
            "feature_items": [
                {
                    "id": "d0ffe2d0-526d-4139-8ff3-cdaacde33129",
                    "name": "Another test 1",
                    "type": "Bug"
                }
            ]
        }
    ]
  }

I've tried using json_agg function by I run into the issue of not being able to chain aggregate functions.

This is the SQL I currently have

SELECT pr.id, pr.name, json_agg(f) as features
            FROM projects pr
            LEFT JOIN features f
            ON f.project_id = pr.id
            LEFT JOIN  feature_items fi
            ON fi.feature_id = f.id
            WHERE pr.id = ANY (:ids ::uuid[])
            GROUP BY pr.id

Mike Organek · Accepted Answer · 2021-02-05 16:01:51Z

1

Do the chained aggregations in either subqueries or CTEs.

The jsonb - operator removes the FK columns from the objects.

with f as (
  select f.id, f.name, f.project_id, jsonb_agg(to_jsonb(fi) - 'feature_id') as feature_items
    from features f
         left join feature_items fi
           on fi.feature_id = f.id
   group by f.id, f.name, f.project_id
), p as (
  select p.id, p.name, jsonb_agg(to_jsonb(f) - 'project_id') as features
    from projects p
         left join f 
           on f.project_id = p.id
   group by p.id, p.name
)
select to_jsonb(p) from p;

answered Feb 5, 2021 at 16:01

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Postgres 3 table join into nested arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related