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. Extract JSON array of numbers from JSON array of objects 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 also like to avoid CROSS JOIN LATERAL because it will slow down the performance.

Answer 1

There is no way around unnesting the array for every row (the drawbacks of de-normalizing data). But you don't need a cross join, an EXISTS condition should work just fine.

select *
from demo d
where exists (select *
 from jsonb_array_elements(d.rawjson) as r(jdoc)
 where (jdoc ->> 'a')::int < 2
 and (jdoc ->> 'b')::int < 8);

If your columns is not a jsonb (which it should be), use json_each() instead

Online example

• user3441114: I am sorry, I tried running this, but I get this error ``` ERROR: operator does not exist: text ->> unknown LINE 8: where (jdoc ->> 'a')::int < 2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ``` here is the db fiddle [link] (dbfiddle.uk/…)
• user1822: Then rawjson is a text not a jsonb (or at least json) column. You will have to cast it then: rawjson::jsonb. You should change the column's data type to jsonb
• Madeo: I can confirm that this is not working
• user1822: @Madeo: you are right, that should be jsonb_array_elements() not jsonb_each(). I corrected the answer