How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres?

Question

Here is an example of the json object

rawJSON = [
   {"a":0, "b":7},
   {"a":1, "b":8}, 
   {"a":2, "b":9}
]

And I have a table that essentially looks like this.

demo Table

id | ...(other columns)      | rawJSON
------------------------------------
0  | ...(other columns info) | [{"a":0, "b":7},{"a":1, "b":8}, {"a":2, "b":9}]
1  | ...(other columns info) | [{"a":0, "b":17},{"a":11, "b":5}, {"a":12, "b":5}]

What I want is to return a row which insideRawJSON has value from "a" of less than 2 AND the value from "b" of less than 8. THEY MUST BE FROM THE SAME JSON OBJECT.

Essentially the query would similarly look like this

SELECT *
FROM demo
WHERE FOR ANY JSON OBJECT in rawJSON column -> "a" < 2 AND -> "b" < 8

And therefore it will return

id | ...(other columns)      | rawJSON
------------------------------------
0  | ...(other columns info) | [{"a":0, "b":7},{"a":1, "b":8}, {"a":2, "b":9}]

I have searched from several posts here but was not able to figure it out. https://dba.stackexchange.com/questions/229069/extract-json-array-of-numbers-from-json-array-of-objects https://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array

I was thinking of creating a plgpsql function but wasn't able to figure out .

Any advice I would greatly appreciate it!

Thank you!!

I would like to avoid cross join lateral because it will slow down a lot.

Answer 1

You can use a subquery that searches through the array elements together with EXISTS.

SELECT *
       FROM demo d
       WHERE EXISTS (SELECT *
                            FROM jsonb_array_elements(d.rawjson) a(e)
                            WHERE (a.e->>'a')::integer < 2
                                  AND (a.e->>'b')::integer < 8);

db<>fiddle

If the datatype for rawjson is json rather than jsonb, use json_array_elements() instead of jsonb_array_elements().