SQL query to filter where all array items in JSONB array meet condition

Question

I made a similar post before, but deleted it as it had contextual errors.

One of the tables in my database includes a JSONB column which includes an array of JSON objects. It's not dissimilar to this example of a session table which I've mocked up below.

id	user_id	snapshot	inserted_at
1	37	{cart: [{product_id: 1, price_in_cents: 3000, name: "product A"}, {product_id: 2, price_in_cents: 2500, name: "product B"}]}	2022-01-01 20:00:00.000000
2	24	{cart: [{product_id: 1, price_in_cents: 3000, name: "product A"}, {product_id: 3, price_in_cents: 5500, name: "product C"}]}	2022-01-02 20:00:00.000000
3	88	{cart: [{product_id: 4, price_in_cents: 1500, name: "product D"}, {product_id: 2, price_in_cents: 2500, name: "product B"}]}	2022-01-03 20:00:00.000000

The query I've worked with to retrieve records from this table is as follows.

SELECT sessions.*
FROM sessions
INNER JOIN LATERAL (
    SELECT *
    FROM jsonb_to_recordset(sessions.snapshot->'cart')
    AS product(
        "product_id" integer,
        "name" varchar,
        "price_in_cents" integer
    )
) AS cart ON true;

I've been trying to update the query above to retrieve only the records in the sessions table for which ALL of the products in the cart have a price_in_cents value of greater than 2000.

To this point, I've not had any success on forming this query but I'd be grateful if anyone here can point me in the right direction.

score 1 · Answer 1 · answered Mar 01 '22 at 15:39

You can use a JSON path expression:

select *
from sessions
  ...
where not sessions.snapshot @@ '$.cart[*].price_in_cents <= 2000'

There is no JSON path expression that would check that all array elements are greater 2000. So this returns those rows where no element is smaller than 2000 - because that can be expressed with a JSON path expression.

BERELLION · Answer 2 · 2022-03-01T20:35:31.080

Here is one possible solution based on the idea of your original query. Each element of the cart JSON array object is joined to its sessions parent row. You 're left adding the WHERE clause conditions now that the wanted JSON array elements are exposed.

SELECT *
FROM (
  SELECT
    sess.id,
    sess.user_id,
    sess.inserted_at,
    cart_items.cart_name,
    cart_items.cart_product_id,
    cart_items.cart_price_in_cents
  FROM sessions sess,
    LATERAL (SELECT (snapshot -> 'cart') snapshot_cart FROM sessions WHERE id = sess.id) snap_arr,
    LATERAL (SELECT
               (value::jsonb ->> 'name')::text cart_name,
               (value::jsonb -> 'product_id')::int cart_product_id,
               (value::jsonb -> 'price_in_cents')::int cart_price_in_cents
             FROM JSONB_ARRAY_ELEMENTS(snap_arr.snapshot_cart)) cart_items
) session_snapshot_cart_product;

Explanation :

From the sessions table, the cart array is exctracted and joined per sessions row
The necessary items of the cart JSON array is then unnested by the second join using the JSONB_ARRAY_ELEMENTS(jsonb) function

score 0 · Accepted Answer · answered Mar 02 '22 at 01:00

The following worked well for me and allowed me the flexibility to use different comparison operators other than just ones such as == or <=.

In one of the scenarios I needed to construct, I also needed to have my WHERE in the subquery also compare against an array of values using the IN comparison operator, which was not viable using some of the other solutions that were looked at.

Leaving this here in case others run into the same issue as I did, or if others find better solutions or want to propose suggestions to build upon this one.

SELECT *
FROM sessions
WHERE NOT EXISTS (
    SELECT sessions.*
    FROM sessions
    INNER JOIN LATERAL (
        SELECT *
        FROM jsonb_to_recordset(sessions.snapshot->'cart')
        AS product(
            "product_id" integer,
            "name" varchar,
            "price_in_cents" integer
        )
    ) AS cart ON true
    WHERE name ILIKE "Product%";
)

SQL query to filter where all array items in JSONB array meet condition

3 Answers3