CODEWITHSUNDEEP
sqljsonpostgresqlaggregate
Nadhas
Nadhas

Reputation: 5807

SQL query with one-to-many relationship with PostgreSQL

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?

Upvotes: 3

Views: 1033

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246513

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?

Upvotes: 8

Related Questions