CODEWITHSUNDEEP
sqljsonpostgresqljoin
Bartłomiej Sobieszek
Bartłomiej Sobieszek

Reputation: 2810

Join records from another table as JSON array

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

Upvotes: 0

Views: 110

Answers (1)

Jeremy
Jeremy

Reputation: 6723

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.

Upvotes: 1

Related Questions