get all the json object with a specified conditions

user_5667911 0
Viewed 609

I have a jsonarray as given below

[{"key1":10},{"key1":20},{"key1":30}]

I want to get all the json objects with a specified condition. say get all the json objects with key1 less than 25. so my sql query should return this list

[{"key1":10},{"key1":20}]

what is the resultant SQL query for this.

jsonb postgresql arrays sql json
2 Answers

step-by-step demo:db<>fiddle

SELECT
    json_agg(elements)                       -- 3
FROM mytable,
    json_array_elements(mydata) as elements  -- 1
WHERE (elements ->> 'key1')::int < 25        -- 2
  1. Extract the JSON array: Each element is now in an own record
  2. Filter by the value. Notice, that ->> returns a type text, so you need to cast it into type int in your case
  3. Reaggregate the remaining elements into the new array
user_39842210

If you are using Postgres 12 or later you can use a JSON path function:

select jsonb_path_query_array(the_column, '$[*] ? (@.key1 <= 25)')
from the_table

Online example

user_3303150
Related