The carts table stores each shopping cart as one document:

{
  "customer": { "name": "Ava Chen", "country": "CA" },
  "status": "open",
  "items": [
    { "sku": "kraken-plush", "qty": 1, "price": 29.99 },
    { "sku": "linux-stickers", "qty": 3, "price": 6.00 }
  ]
}

Read a key: -> and ->>

Use -> to go into an object or array. Use ->> for the last step, to get text that you can compare with = or LIKE:

SELECT id, doc -> 'customer' ->> 'name' AS customer
FROM carts
WHERE doc ->> 'status' = 'open';

->> always returns text. To compare a number, convert it with ::numeric or ::integer: (doc -> 'items' -> 0 ->> 'qty')::integer > 2.

Contains: @>

a @> b is true when document a contains everything in document b:

SELECT id FROM carts WHERE doc @> '{"status": "paid"}';

It also looks inside nested objects: doc @> '{"customer": {"country": "CA"}}'.

One row per array element

jsonb_array_elements(array) returns one row for each element. Put it in the FROM with a CROSS JOIN (Chapter 8), and give it an alias:

SELECT c.id, item ->> 'sku' AS sku
FROM carts c
CROSS JOIN jsonb_array_elements(c.doc -> 'items') AS item;

A cart with an empty array returns no rows.