Conditions and operators

Basic examples

Condition	SQL
{id: 2}	"id" = 2
{'id <': 2}	"id" < 2
{'id >': 2}	"id" > 2
{'id <=': 2}	"id" <= 2
{'id >=': 2}	"id" >= 2
{'id !=': 2}	"id" != 2
{'id <>': 2}	"id" != 2
{'id': null}	"id" is null
{'id !=': null}	"id" is not null
{'id is not': null}	"id" is not null
{'id in':[1,2,3]}	"id" in (1,2,3)
{'id not in':[1,2,3]}	"id" not in (1,2,3)

Extended

Condition	SQL
{id: [1,2,3]}	"id" in (1,2,3)
{'id <>': [1,2,3]}	"id" not in (1,2,3)
{'id =*':'gamma'}	LOWER("id") = LOWER('gamma')
{'fruits =*':'KiWi'}	LOWER("fruits") && '{kiwi}' //Has to have the LOWER function defined see below. (It uses GIN index if defined on fruits)

Pattern matching

PostgreSQL Documentation

Condition	SQL
{'id ~~': 'a%'}	"id" LIKE 'a%'
{'id !~~': 'a%'}	"id" NOT LIKE 'a%'
{'id like': 'a%'}	"id" LIKE 'a%'
{'id not like': 'a%'}	"id" NOT LIKE 'a%'
{'id ~~*': 'a%'}	"id" ILIKE 'a%'
{'id !~~*': 'a%'}	"id" NOT ILIKE 'a%'
{'id ilike': 'a%'}	"id" ILIKE 'a%'
{'id not ilike': 'a%'}	"id" NOT ILIKE 'a%'
{'id similar to': 'a%'}	"id" SIMILAR TO 'a%'
{'id not similar to': 'a%'}	"id" NOT SIMILAR TO 'a%'
{'id ~': '^a'}	"id" ~ '^a'
{'id !~': '^a'}	"id" !~ '^a'
{'id ~*': '^a'}	"id" ~* '^a'
{'id !~*': '^a'}	"id" !~* '^a'
{'id is distinct from': '^a'}	"id" IS DISTINCT FROM '^a'
{'id is not distinct from': '^a'}	"id" IS NOT DISTINCT FROM '^a'

Extended

Condition	SQL
{'id ~~': ['1%','2%','3%']}	"id" LIKE ANY('{1%,2%,3%}')
{'id !~~': ['1%','2%','3%']}	"id" NOT LIKE ALL('{1%,2%,3%}')
{'id ~': ['^1','^2','^3']}	"id" LIKE ANY('{^1,^2,^3}')
{'id !~': ['^1','^2','^3']}	"id" NOT LIKE ALL('{^1,^2,^3}')

Array type

PostgreSQL Documentation

Condition	SQL	note
{'ids @>':[1,2,3]}	"ids" @> '{1,2,3}'	ids contains all of 1,2,3
{'ids <@':[1,2,3]}	"ids" <@ '{1,2,3}'	ids is a subset of {1,2,3}
{'ids &&':[1,2,3]}	"ids" && '{1,2,3}'	ids overlaps with {1,2,3}
{'ids': [1,2,3]}	"ids" = '{1,2,3}'
{'ids': 'a'}	'a' = ANY("ids")
{'ids <>': 'a'}	'a' <> ANY("ids")

Please note that as-of-9.5 'a' = ANY("ids") is not using the index in psql. So if have a GIN index is defined on the column "ids", You should use {'ids &&': ['a']} (that is translated to "ids" && '{a}').

Extended

Condition	SQL
{'ids ~': 'a%'}	EXISTS (SELECT * FROM (SELECT UNNEST("ids") _el) _arr WHERE _arr._el ~ 'a%')'; //same with all pattern matching operator
{'names &&*': ['A','B','C']}	LOWER("names") && '{a, b, c}' //case insensitive overlap operator, has to have the LOWER function defined for arrays (see below)

Jsonb type

PostgreSQL Documentation

Condition	SQL	NOTE
{'jdata @>':[1,2,3]}	"jdata" @> '[1,2,3]'
{'jdata @>':{a:1}}	"jdata" @> '{a:1}'
{'jdata <@':[1,2,3]}	"jdata" <@ '[1,2,3]'
{'jdata <@':{a:1}}	"jdata" <@ '{a:1}'
{'jdata ?':'a'}	"jdata" ? 'a'
{'jdata ?|':['a','b']}	"jdata" ?| '{a,b}'
{'jdata ?&':['a','b']}	"jdata" ?& '{a,b}'
{'jdata -> a':{a:3}}	"jdata" -> 'a' = '{"a":3}'::jsonb
{'jdata -> 3':{a:3}}	"jdata" -> 3 = '{"a":3}'::jsonb	if the field is a number, the quote wont apply as it could refer index also
{"jdata -> '3'":{a:3}}	"jdata" -> '3' = '{"a":3}'::jsonb	... so you have to apply that manually
{'jdata ->> a':3}	"jdata" ->>'a' = 3
{'jdata ->> 3':3}	"jdata" ->> 3 = 3	if the field is a number, the quote wont apply as it could refer index also
{'jdata ->> '3'":'{"a": 3}'}	"jdata"->>3 = '{"a": 3}'	... so you have to apply that manually
{"jdata #> '{a,3}'":{a:3}}	"jdata"#>'{a,3}' = '{"a":3}'::jsonb	surronding {} with ' is possible but not obligatory
{"jdata #>> {a,3}":'{"a": 3}'}	"jdata"#>>'{a,3}' = '{"a": 3}'
{"jdata #>> {a} >":'a'	"jdata"#>>'{a}' > 'a'

Note:

->,#> returns as a JsonObject

->>, #>> returns as a string.

At the moment they are not converted automatically thou, for ->> you should use JSON.stringify if needed. (this is a later todo, you can help us! :), same goes for ->, it not converted automaticaly if not an object passed in, but a number or string)

AND - OR

condition-expressions can be joined together e.g.:

Condition	SQL
{id:1, name:'a'}	id=1 AND name='a'
{or: [{id:1}, {name:'a'}]}	id=1 OR name='a'
{and: [      or: [{id:1}, {'port >':'1024'}],      or: [{host:'localhost', os:'linux'}, {host:'127.0.0.1'}] ]}	(.. OR ..) AND ((.. AND ..) OR ..)

Definition of LOWER function for array type

CREATE OR REPLACE FUNCTION LOWER(text[]) RETURNS text[] LANGUAGE SQL IMMUTABLE AS $$ SELECT array_agg(LOWER(value)) FROM unnest($1) value; $$;