SQL query with one-to-many relationship with PostgreSQL

Question

I'm trying to aggregate a list of cart items with a query that relates through a line_items table. I've abstracted a simple example of my use case:

my expected result would look like this:

cart_id  cart_total shopper_id items                                                        payment_id
      1         345         34 [{"name":"egg","price:"39"}]                                 AS34gsdVSWET4
      2          54         45 [{"name":"biscut","price:"5"},{"name":"apple","price:"15"}]  JFHERHJE$Y#$H

given a schema and data like:

carts:

id  cart_total shopper_id
1   39         34
2   20         45

line_items:

id  cart_id    item_name item_price
1   1          egg       39
2   2          biscut     5
3   2          apple     15

payment:

id  cart_id payment_id
1   1       AS34gsdVSWET4
2   2       JFHERHJE$Y#$H

How to get all cart list and get a list of carts for particular shopperId?

Laurenz Albe · Accepted Answer · 2019-03-04 07:53:54Z

8

Here is my solution using json_build_object:

SELECT c.id AS cart_id,
       c.cart_total,
       c.shopper_id,
       json_agg(
          json_build_object(
             'item_name', i.item_name,
             'item_price', i.item_price::text
          )
       ) AS items,
       p.payment_id
FROM carts AS c
   JOIN line_items AS i ON i.cart_id = c.id
   JOIN payment AS p ON p.cart_id = c.id
GROUP BY c.id, c.cart_total, c.shopper_id, p.payment_id;

Are you sure you want the price as string?

edited Mar 4, 2019 at 7:53

answered Mar 4, 2019 at 5:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

Nadhas Over a year ago

Not required, do we need to include all fields in GROUP BY?

Nadhas Over a year ago

Your query results: items: [{"egg":39}], but I need [{"item_name":"egg","item_price":39}]

Laurenz Albe Over a year ago

That was easily fixed. Retain the GROUP BY items.

Collectives™ on Stack Overflow

SQL query with one-to-many relationship with PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related