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

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

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