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.

Answer 1

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%";
)