Join records from another table as JSON array

Question

We have the two tables: "product" and "product_part". One product can have many product parts, so that the product_part has id_product column that refernces a product's id column.

I would really like to create a MATERIALIZED VIEW that from this data set:

| Product | Product Part|
+-----------------------+
| Gearbox | Metal scrap |
+-----------------------+
| Gearbox | Iron scrap  |
+-----------------------+
| Gearbox | Gold scrap  |
+-----------------------+

Would create this output:

| Product | Parts                                          |
+---------+------------------------------------------------+
| Gearbox | ["Metal scrap", "Iron scrap", "Gold scrap"]    |
+---------+------------------------------------------------+

Jeremy · Accepted Answer · 2020-02-22 02:03:44Z

1

This would just be:

SELECT product.name, json_agg(product_part.name) as parts
FROM product
JOIN product_part ON product.id = product_part.product_id
GROUP BY product.id, product.name;

I had to guess a bit about your actual columns, but this should be close.

answered Feb 22, 2020 at 2:03

Jeremy

6,77322 silver badges21 bronze badges

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

1 Comment

Bartłomiej Sobieszek Over a year ago

Thank you. I think I may use it nicely to optimize app when combined with materialized views.

Collectives™ on Stack Overflow

Join records from another table as JSON array

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