Postgres jsonb_insert array

Question

I am trying to construct a query that returns an array of customer carts and all of their items, e.g.

Tables:

cart: id, customer_id

item: cart_id, price, brand

Desired JSON result example:

[{
  "id": 1, "customer_id": 1,
  "items": [{
    "price": 14.50, "brand": "Coke"
  }]
}]

Failing SQL Query (Testable here: http://sqlfiddle.com/#!17/0ef54/1):

SELECT jsonb_insert(to_jsonb(c.*), 'items', array(
  SELECT to_jsonb(i.*)
  FROM item i
  WHERE i.cart_id = c.id
)
FROM cart c

How can one achieve this?

score 1 · Accepted Answer · 2021-01-14 14:21:10Z

1

Sounds as if you are looking for an aggregation:

select jsonb_agg(to_jsonb(c)||jsonb_build_object('items', i.items))
from cart c 
  join (
    select cart_id, jsonb_agg(to_jsonb(i) - 'cart_id') as items
    from item i
    group by cart_id
  ) i on i.cart_id = c.id;

Online example

edited Jan 14, 2021 at 14:21

answered Jan 14, 2021 at 13:57

user330315

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

2 Comments

Werner Raath Over a year ago

Tried that, threw an error: sqlfiddle.com/#!17/0ef54/2

user330315 Over a year ago

@Werner: sorry, forgot the alias. See my edit

Stefanov.sm · Accepted Answer · 2021-01-14 14:31:01Z

select jsonb_agg(to_jsonb(c.*) || 
 jsonb_build_object
 (
  'items', 
  (select jsonb_agg(to_jsonb(i.*) - 'cart_id') from item i where c.id = i.cart_id)
 )
) from cart c;

Demo

create temporary table item (cart_id integer, price numeric, brand text);
create temporary table cart (id integer, customer_id integer);
insert into item values (1, 10, 'skoda'),(1, 11, 'ford'), (1, 12, 'opel'); 
insert into item values (2, 20, 'skoda'),(2, 21, 'ford'), (2, 22, 'opel'); 
insert into cart values (1, 1), (2, 2);

select jsonb_agg(to_jsonb(c.*) || jsonb_build_object(
 'items', (select jsonb_agg(to_jsonb(i.*) - 'cart_id') from item i where c.id = i.cart_id)
)) from cart c;

Result:

[
  {
    "id": 1,
    "items": [
      {
        "brand": "skoda",
        "price": 10
      },
      {
        "brand": "ford",
        "price": 11
      },
      {
        "brand": "opel",
        "price": 12
      }
    ],
    "customer_id": 1
  },
  {
    "id": 2,
    "items": [
      {
        "brand": "skoda",
        "price": 20
      },
      {
        "brand": "ford",
        "price": 21
      },
      {
        "brand": "opel",
        "price": 22
      }
    ],
    "customer_id": 2
  }
]

Collectives™ on Stack Overflow

Postgres jsonb_insert array

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related