Use "json_field@>" for greater than, is in etc (PostgreSQL)

user_6224975 0
Viewed 85

Say I have a table in a PostgreSQL database like

id | json_field
---+-----------
1    {"name":"foo","age":20}
2    {"name":"bar", "age":10, "alive":1}
.
.

I can get the data for json_field where name=="foo" by doing

SELECT * from my_table where json_field@>"{'name':'foo'}"

or

SELECT * from my_table where (json_field->>'name')='foo'"

Since the first query is way faster (it is indexed as far as I understand, where the second have to loop through each record and check) is there a way to do other conditons like "greater than", "is in", "is not" e.g (some pseudo-code)

SELECT * from my_table where json_field@>"{'name':is not 'foo'}"
SELECT * from my_table where json_field@>"{'name':>'foo'}"
SELECT * from my_table where json_field@>"{'age':is in '(20,50)'}"
postgresql
1 Answers

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

SELECT * 
from my_table 
where json_field @@ '$.name <> "foo"';

SELECT * 
from my_table 
where json_field @@ '$.age == 20 || $.age == 50'

If you are using an older Postgres version, you will need to extract the values and use "normal" SQL expressions:

SELECT * 
from my_table 
where json_field ->> 'name' <> 'foo';


SELECT * 
from my_table 
where (json_field ->> 'age')::int in (20,50);

To make that efficient you will have to create indexes for those expressions:

create index on my_table ( (json_field ->> 'name') );
create index on my_table ( ((json_field ->> 'age')::int) );

That's the price you have to pay for de-normalizing your data model.

user_3303150
Related