0

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.

1 Answer 1

3

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

5
  • 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/…) Dec 12, 2019 at 13:02
  • 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 Dec 12, 2019 at 13:15
  • I can confirm that this is not working
    – Madeo
    Nov 27, 2020 at 9:58
  • 1
    @Madeo: you are right, that should be jsonb_array_elements() not jsonb_each(). I corrected the answer Nov 27, 2020 at 10:15
  • Yes, not is working, thanks
    – Madeo
    Nov 27, 2020 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.