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

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

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) );

Thats the price you have to pay for de-normalizing your data model.

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

Leave a Reply

Your email address will not be published.