CREATE EXTENSION jsquery; set escape_string_warning=off; CREATE TABLE test_jsquery (v jsonb); \copy test_jsquery from 'data/test_jsquery.data' select 'asd.zzz = 13'::jsquery; jsquery ------------------ "asd"."zzz" = 13 (1 row) select 'asd.zzz < 13'::jsquery; jsquery ------------------ "asd"."zzz" < 13 (1 row) select 'asd(zzz < 13)'::jsquery; jsquery ------------------ "asd"."zzz" < 13 (1 row) select 'asd(zzz < 13 AND x = true)'::jsquery; jsquery ---------------------------------- "asd"("zzz" < 13 AND "x" = true) (1 row) select 'asd(zzz < 13 AND x = "true")'::jsquery; jsquery ------------------------------------ "asd"("zzz" < 13 AND "x" = "true") (1 row) select 'asd(zzz < 13 AND x.zxc = "true")'::jsquery; jsquery ------------------------------------------ "asd"("zzz" < 13 AND "x"."zxc" = "true") (1 row) select 'asd(zzz < 13 OR #.zxc = "true" /* test */)'::jsquery; jsquery --------------------------------------- "asd"("zzz" < 13 OR #."zxc" = "true") (1 row) select 'asd(* < 13 AND /* ttt */ #.zxc = "true")'::jsquery; jsquery ------------------------------------ "asd"(* < 13 AND #."zxc" = "true") (1 row) select '(* < 13 AND #.zxc = "true")'::jsquery; jsquery ------------------------------- (* < 13 AND #."zxc" = "true") (1 row) select '* < 13 AND #.zxc/* t2 */ = "true"'::jsquery; jsquery ------------------------------- (* < 13 AND #."zxc" = "true") (1 row) select '* < 13 AND #.zxc"x" = "true"'::jsquery; ERROR: bad jsquery representation LINE 1: select '* < 13 AND #.zxc"x" = "true"'::jsquery; ^ DETAIL: syntax error, unexpected STRING_P at or near """ select 'a < 1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < -1'::jsquery; jsquery ---------- "a" < -1 (1 row) select 'a < +1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < .1'::jsquery; jsquery ----------- "a" < 0.1 (1 row) select 'a < -.1'::jsquery; jsquery ------------ "a" < -0.1 (1 row) select 'a < +.1'::jsquery; jsquery ----------- "a" < 0.1 (1 row) select 'a < 0.1'::jsquery; jsquery ----------- "a" < 0.1 (1 row) select 'a < -0.1'::jsquery; jsquery ------------ "a" < -0.1 (1 row) select 'a < +0.1'::jsquery; jsquery ----------- "a" < 0.1 (1 row) select 'a < 10.1'::jsquery; jsquery ------------ "a" < 10.1 (1 row) select 'a < -10.1'::jsquery; jsquery ------------- "a" < -10.1 (1 row) select 'a < +10.1'::jsquery; jsquery ------------ "a" < 10.1 (1 row) select 'a < 1e1'::jsquery; jsquery ---------- "a" < 10 (1 row) select 'a < -1e1'::jsquery; jsquery ----------- "a" < -10 (1 row) select 'a < +1e1'::jsquery; jsquery ---------- "a" < 10 (1 row) select 'a < .1e1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < -.1e1'::jsquery; jsquery ---------- "a" < -1 (1 row) select 'a < +.1e1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < 0.1e1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < -0.1e1'::jsquery; jsquery ---------- "a" < -1 (1 row) select 'a < +0.1e1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < 10.1e1'::jsquery; jsquery ----------- "a" < 101 (1 row) select 'a < -10.1e1'::jsquery; jsquery ------------ "a" < -101 (1 row) select 'a < +10.1e1'::jsquery; jsquery ----------- "a" < 101 (1 row) select 'a < 1e-1'::jsquery; jsquery ----------- "a" < 0.1 (1 row) select 'a < -1e-1'::jsquery; jsquery ------------ "a" < -0.1 (1 row) select 'a < +1e-1'::jsquery; jsquery ----------- "a" < 0.1 (1 row) select 'a < .1e-1'::jsquery; jsquery ------------ "a" < 0.01 (1 row) select 'a < -.1e-1'::jsquery; jsquery ------------- "a" < -0.01 (1 row) select 'a < +.1e-1'::jsquery; jsquery ------------ "a" < 0.01 (1 row) select 'a < 0.1e-1'::jsquery; jsquery ------------ "a" < 0.01 (1 row) select 'a < -0.1e-1'::jsquery; jsquery ------------- "a" < -0.01 (1 row) select 'a < +0.1e-1'::jsquery; jsquery ------------ "a" < 0.01 (1 row) select 'a < 10.1e-1'::jsquery; jsquery ------------ "a" < 1.01 (1 row) select 'a < -10.1e-1'::jsquery; jsquery ------------- "a" < -1.01 (1 row) select 'a < +10.1e-1'::jsquery; jsquery ------------ "a" < 1.01 (1 row) select 'a < 1e+1'::jsquery; jsquery ---------- "a" < 10 (1 row) select 'a < -1e+1'::jsquery; jsquery ----------- "a" < -10 (1 row) select 'a < +1e+1'::jsquery; jsquery ---------- "a" < 10 (1 row) select 'a < .1e+1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < -.1e+1'::jsquery; jsquery ---------- "a" < -1 (1 row) select 'a < +.1e+1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < 0.1e+1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < -0.1e+1'::jsquery; jsquery ---------- "a" < -1 (1 row) select 'a < +0.1e+1'::jsquery; jsquery --------- "a" < 1 (1 row) select 'a < 10.1e+1'::jsquery; jsquery ----------- "a" < 101 (1 row) select 'a < -10.1e+1'::jsquery; jsquery ------------ "a" < -101 (1 row) select 'a < +10.1e+1'::jsquery; jsquery ----------- "a" < 101 (1 row) select 'a in (0,1,2)'::jsquery; jsquery ------------------ "a" IN (0, 1, 2) (1 row) select 'a IN (0,null, "null", xxx, "zzz", 2)'::jsquery; jsquery ------------------------------------------- "a" IN (0, null, "null", "xxx", "zzz", 2) (1 row) select 'not < 1'::jsquery; jsquery ----------- "not" < 1 (1 row) select 'not not < 1'::jsquery; jsquery ----------------- NOT ("not" < 1) (1 row) select 'not( not < 1)'::jsquery; jsquery ----------------- NOT ("not" < 1) (1 row) select 'not.x < 1'::jsquery; jsquery --------------- "not"."x" < 1 (1 row) select 'x.not < 1'::jsquery; jsquery --------------- "x"."not" < 1 (1 row) select 'is < 1'::jsquery; jsquery ---------- "is" < 1 (1 row) select 'in < 1'::jsquery; jsquery ---------- "in" < 1 (1 row) select 'not is < 1'::jsquery; jsquery ---------------- NOT ("is" < 1) (1 row) select 'not in < 1'::jsquery; jsquery ---------------- NOT ("in" < 1) (1 row) select 'in in (1,2)'::jsquery; jsquery ---------------- "in" IN (1, 2) (1 row) select 'is in (1,2)'::jsquery; jsquery ---------------- "is" IN (1, 2) (1 row) select 'not in (1,2)'::jsquery; jsquery ----------------- "not" IN (1, 2) (1 row) select 'in is numeric'::jsquery; jsquery ----------------- "in" IS NUMERIC (1 row) select 'is is numeric'::jsquery; jsquery ----------------- "is" IS NUMERIC (1 row) select 'not is numeric'::jsquery; jsquery ------------------ "not" IS NUMERIC (1 row) select 'not.in < 1'::jsquery; jsquery ---------------- "not"."in" < 1 (1 row) select 'not.is < 1'::jsquery; jsquery ---------------- "not"."is" < 1 (1 row) select 'not.not < 1'::jsquery; jsquery ----------------- "not"."not" < 1 (1 row) select 'in.in < 1'::jsquery; jsquery --------------- "in"."in" < 1 (1 row) select 'in.is < 1'::jsquery; jsquery --------------- "in"."is" < 1 (1 row) select 'in.not < 1'::jsquery; jsquery ---------------- "in"."not" < 1 (1 row) select 'is.in < 1'::jsquery; jsquery --------------- "is"."in" < 1 (1 row) select 'is.is < 1'::jsquery; jsquery --------------- "is"."is" < 1 (1 row) select 'is.not < 1'::jsquery; jsquery ---------------- "is"."not" < 1 (1 row) select '{"a": {"b": null}}'::jsonb @@ 'a.b = 1'; ?column? ---------- f (1 row) select '{"a": {"b": null}}'::jsonb @@ 'a.b = null'; ?column? ---------- t (1 row) select '{"a": {"b": null}}'::jsonb @@ 'a.b = false'; ?column? ---------- f (1 row) select '{"a": {"b": false}}'::jsonb @@ 'a.b = false'; ?column? ---------- t (1 row) select '{"a": {"b": false}}'::jsonb @@ 'a.b = true'; ?column? ---------- f (1 row) select '{"a": {"b": true}}'::jsonb @@ 'a.b = true'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b = 1'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b < 1'; ?column? ---------- f (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b <= 1'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b >= 1'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b > 1'; ?column? ---------- f (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b = 2'; ?column? ---------- f (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b < 2'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b <= 2'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b >= 2'; ?column? ---------- f (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b > 2'; ?column? ---------- f (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b = 0'; ?column? ---------- f (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b < 0'; ?column? ---------- f (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b <= 0'; ?column? ---------- f (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b >= 0'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.b > 0'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ '*.b > 0'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ '*.b > 0'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.* > 0'; ?column? ---------- t (1 row) select '{"a": {"b": 1}}'::jsonb @@ 'a.* > 0'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.b && [ 1 ]'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.b @> [ 1 ]'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.b <@ [ 1 ]'; ?column? ---------- f (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.b @> [ 1,2,3,4 ]'; ?column? ---------- f (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.b <@ [ 1,2,3,4 ]'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}]'::jsonb @@ '*.a = 4'; ?column? ---------- f (1 row) select '[{"a": 2}, {"a": 3}]'::jsonb @@ '*.a = 3'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '#.a = 4'; ?column? ---------- f (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '*.a = 4'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '#(a = 1 OR a=3)'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '#(a = 3 OR a=1)'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '#(a = 3 and a=1)'; ?column? ---------- f (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '#(a = 3 and a=2)' as "false"; false ------- f (1 row) select '[{"a": 2, "b":3}, {"a": 3, "b": 1}]'::jsonb @@ '#(b = 1 and a=3)'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '#.a.a = 4'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '*.a.a = 4'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '*.#.a.a = 4'; ?column? ---------- t (1 row) select '[{"a": 2}, {"a": 3}, {"a": {"a":4}}]'::jsonb @@ '#.*.a.a = 4'; ?column? ---------- t (1 row) select '{"a": 1}'::jsonb @@ 'a in (0,1,2)'; ?column? ---------- t (1 row) select '{"a": 1}'::jsonb @@ 'a in (0,2)'; ?column? ---------- f (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b.#=2'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.b && [ 5 ]'; ?column? ---------- f (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a=*'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b=*'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.c=*'; ?column? ---------- f (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b = [1,2,3]'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b.# = [1,2,3]'; ?column? ---------- f (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b && [1,2,3]'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b.# && [1,2,3]'; ?column? ---------- f (1 row) select 'asd.# = 3'::jsquery & 'zzz = true' | 'xxx.# = zero'; ?column? ------------------------------------------------------ (("asd".# = 3 AND "zzz" = true) OR "xxx".# = "zero") (1 row) select !'asd.# = 3'::jsquery & 'zzz = true' | !'xxx.# = zero'; ?column? ------------------------------------------------------------------ ((NOT ("asd".# = 3) AND "zzz" = true) OR NOT ("xxx".# = "zero")) (1 row) select '{"x":[0,1,1,2]}'::jsonb @@ 'x @> [1,0]'::jsquery; ?column? ---------- t (1 row) select '{"x":[0,1,1,2]}'::jsonb @@ 'x @> [1,0,1]'::jsquery; ?column? ---------- t (1 row) select '{"x":[0,1,1,2]}'::jsonb @@ 'x @> [1,0,3]'::jsquery; ?column? ---------- f (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.b && [ 2 ]'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.b($ && [ 2 ])'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.$.b && [ 2 ]'; ?column? ---------- t (1 row) select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.$.b ($ && [ 2 ])'; ?column? ---------- t (1 row) select '[1,2,3]'::jsonb @@ '# && [2]'; ?column? ---------- f (1 row) select '[1,2,3]'::jsonb @@ '#($ && [2])'; ?column? ---------- f (1 row) select '[1,2,3]'::jsonb @@ '$ && [2]'; ?column? ---------- t (1 row) select '[1,2,3]'::jsonb @@ '$ ($ && [2])'; ?column? ---------- t (1 row) select '[1,2,3]'::jsonb @@ '$ = 2'; ?column? ---------- f (1 row) select '[1,2,3]'::jsonb @@ '# = 2'; ?column? ---------- t (1 row) select '[1,2,3]'::jsonb @@ '#.$ = 2'; ?column? ---------- t (1 row) select '[1,2,3]'::jsonb @@ '#($ = 2)'; ?column? ---------- t (1 row) select '[3,4]'::jsonb @@ '#($ > 2 and $ < 5)'; ?column? ---------- t (1 row) select '[3,4]'::jsonb @@ '# > 2 and # < 5'; ?column? ---------- t (1 row) select '[1,6]'::jsonb @@ '#($ > 2 and $ < 5)'; ?column? ---------- f (1 row) select '[1,6]'::jsonb @@ '# > 2 and # < 5'; ?column? ---------- t (1 row) select '{"a": {"b": 3, "c": "hey"}, "x": [5,6]}'::jsonb @@ '%.b=3'; ?column? ---------- t (1 row) select '{"a": {"b": 3, "c": "hey"}, "x": [5,6]}'::jsonb @@ 'a.%=3'; ?column? ---------- t (1 row) select '{"a": {"b": 3, "c": "hey"}, "x": [5,6]}'::jsonb @@ '%.%="hey"'; ?column? ---------- t (1 row) select '{"a": {"b": 3, "c": "hey"}, "x": [5,6]}'::jsonb @@ '%="hey"'; ?column? ---------- f (1 row) select '{"a": {"b": 3, "c": "hey"}, "x": [5,6]}'::jsonb @@ '%=[5,6]'; ?column? ---------- t (1 row) select '"XXX"'::jsonb @@ '$="XXX"'; ?column? ---------- t (1 row) select '"XXX"'::jsonb @@ '#.$="XXX"'; ?column? ---------- f (1 row) --Unicode select 'a\t = "dollar \u0024 character"'::jsquery; jsquery ------------------------------ "a\t" = "dollar $ character" (1 row) select '{ "a": "dollar \u0024 character" }'::jsonb @@ '* = "dollar \u0024 character"'; ?column? ---------- t (1 row) select '{ "a": "dollar \u0024 character" }'::jsonb @@ '* = "dollar $ character"'; ?column? ---------- t (1 row) select '{ "a": "dollar $ character" }'::jsonb @@ '* = "dollar \u0024 character"'; ?column? ---------- t (1 row) select 'a\r = "\n\""'::jsquery; jsquery ---------------- "a\r" = "\n\"" (1 row) select 'a\r = "\u0000"'::jsquery; ERROR: unsupported Unicode escape sequence LINE 1: select 'a\r = "\u0000"'::jsquery; ^ DETAIL: \u0000 cannot be converted to text. select 'a\r = \u0000'::jsquery; ERROR: unsupported Unicode escape sequence LINE 1: select 'a\r = \u0000'::jsquery; ^ DETAIL: \u0000 cannot be converted to text. select 'a\r = "\abcd"'::jsquery AS err; ERROR: bad jsquery representation LINE 1: select 'a\r = "\abcd"'::jsquery AS err; ^ DETAIL: Escape sequence is invalid at or near "\a" select 'a\r = "\\abcd"'::jsquery; jsquery ------------------ "a\r" = "\\abcd" (1 row) select 'a\r = "x\u0000"'::jsquery; ERROR: unsupported Unicode escape sequence LINE 1: select 'a\r = "x\u0000"'::jsquery; ^ DETAIL: \u0000 cannot be converted to text. select 'a\r = x\u0000'::jsquery; ERROR: unsupported Unicode escape sequence LINE 1: select 'a\r = x\u0000'::jsquery; ^ DETAIL: \u0000 cannot be converted to text. select 'a\r = "x\abcd"'::jsquery AS err; ERROR: bad jsquery representation LINE 1: select 'a\r = "x\abcd"'::jsquery AS err; ^ DETAIL: Escape sequence is invalid at or near "\a" select 'a\r = "x\\abcd"'::jsquery; jsquery ------------------- "a\r" = "x\\abcd" (1 row) select 'a\r = "x\u0000x"'::jsquery; ERROR: unsupported Unicode escape sequence LINE 1: select 'a\r = "x\u0000x"'::jsquery; ^ DETAIL: \u0000 cannot be converted to text. select 'a\r = x\u0000x'::jsquery; ERROR: unsupported Unicode escape sequence LINE 1: select 'a\r = x\u0000x'::jsquery; ^ DETAIL: \u0000 cannot be converted to text. select 'a\r = "x\abcdx"'::jsquery AS err; ERROR: bad jsquery representation LINE 1: select 'a\r = "x\abcdx"'::jsquery AS err; ^ DETAIL: Escape sequence is invalid at or near "\a" select 'a\r = "x\\abcdx"'::jsquery; jsquery -------------------- "a\r" = "x\\abcdx" (1 row) select 'a\r = "\u0000x"'::jsquery; ERROR: unsupported Unicode escape sequence LINE 1: select 'a\r = "\u0000x"'::jsquery; ^ DETAIL: \u0000 cannot be converted to text. select 'a\r = \u0000x'::jsquery; ERROR: unsupported Unicode escape sequence LINE 1: select 'a\r = \u0000x'::jsquery; ^ DETAIL: \u0000 cannot be converted to text. select 'a\r = "\abcdx"'::jsquery AS err; ERROR: bad jsquery representation LINE 1: select 'a\r = "\abcdx"'::jsquery AS err; ^ DETAIL: Escape sequence is invalid at or near "\a" select 'a\r = "\\abcdx"'::jsquery; jsquery ------------------- "a\r" = "\\abcdx" (1 row) select 'a\r = x"\\abcd"'::jsquery AS err; ERROR: bad jsquery representation LINE 1: select 'a\r = x"\\abcd"'::jsquery AS err; ^ DETAIL: syntax error, unexpected STRING_P, expecting $end at or near """ --IS select 'as IS boolean OR as is ARRAY OR as is ObJect OR as is Numeric OR as is string'::jsquery; jsquery ------------------------------------------------------------------------------------------------- (((("as" IS BOOLEAN OR "as" IS ARRAY) OR "as" IS OBJECT) OR "as" IS NUMERIC) OR "as" IS STRING) (1 row) select '{"as": "xxx"}' @@ 'as IS string'::jsquery; ?column? ---------- t (1 row) select '{"as": "xxx"}' @@ 'as IS boolean OR as is ARRAY OR as is ObJect OR as is Numeric'::jsquery; ?column? ---------- f (1 row) select '{"as": 5}' @@ 'as is Numeric'::jsquery; ?column? ---------- t (1 row) select '{"as": true}' @@ 'as is boolean'::jsquery; ?column? ---------- t (1 row) select '{"as": false}' @@ 'as is boolean'::jsquery; ?column? ---------- t (1 row) select '{"as": "false"}' @@ 'as is boolean'::jsquery; ?column? ---------- f (1 row) select '["xxx"]' @@ '$ IS array'::jsquery; ?column? ---------- t (1 row) select '{"as": false}' @@ '$ IS object'::jsquery; ?column? ---------- t (1 row) select '"xxx"' @@ '$ IS string'::jsquery; ?column? ---------- t (1 row) select '"xxx"' @@ '$ IS numeric'::jsquery; ?column? ---------- f (1 row) --hint select 'a /*-- noindex */ = 5'::jsquery; jsquery -------------------------- "a" /*-- noindex */ = 5 (1 row) select 'a /*-- index */ = 5'::jsquery; jsquery ------------------------ "a" /*-- index */ = 5 (1 row) select 'asd.# = 3'::jsquery & 'zzz /*-- noindex */ = true' | 'xxx.# /*-- index */ = zero'; ?column? -------------------------------------------------------------------------------------- (("asd".# = 3 AND "zzz" /*-- noindex */ = true) OR "xxx".# /*-- index */ = "zero") (1 row) select 'a /*-- xxx */ = 5'::jsquery; jsquery --------- "a" = 5 (1 row) select 'a /* index */ = 5'::jsquery; jsquery --------- "a" = 5 (1 row) select 'a /* noindex */ = 5'::jsquery; jsquery --------- "a" = 5 (1 row) select 'a = /*-- noindex */ 5'::jsquery; ERROR: bad jsquery representation LINE 1: select 'a = /*-- noindex */ 5'::jsquery; ^ DETAIL: syntax error, unexpected HINT_P at or near "*/" select 'a = /* noindex */ 5'::jsquery; jsquery --------- "a" = 5 (1 row) --LENGTH select 'a.@# = 4'::jsquery; jsquery ------------ "a".@# = 4 (1 row) select 'a.@#.$ = 4'::jsquery as noerror; noerror -------------- "a".@#.$ = 4 (1 row) select 'a.@#.a = 4'::jsquery as error; ERROR: Array length should be last in path LINE 1: select 'a.@#.a = 4'::jsquery as error; ^ select 'a.@#.* = 4'::jsquery as error; ERROR: Array length should be last in path LINE 1: select 'a.@#.* = 4'::jsquery as error; ^ select 'a.@#.% = 4'::jsquery as error; ERROR: Array length should be last in path LINE 1: select 'a.@#.% = 4'::jsquery as error; ^ select 'a.@#.# = 4'::jsquery as error; ERROR: Array length should be last in path LINE 1: select 'a.@#.# = 4'::jsquery as error; ^ select 'a.@#.*: = 4'::jsquery as error; ERROR: Array length should be last in path LINE 1: select 'a.@#.*: = 4'::jsquery as error; ^ select 'a.@#.%: = 4'::jsquery as error; ERROR: Array length should be last in path LINE 1: select 'a.@#.%: = 4'::jsquery as error; ^ select 'a.@#.#: = 4'::jsquery as error; ERROR: Array length should be last in path LINE 1: select 'a.@#.#: = 4'::jsquery as error; ^ select 'a.@# (a = 5 or b = 6)'::jsquery as error; ERROR: Array length should be last in path LINE 1: select 'a.@# (a = 5 or b = 6)'::jsquery as error; ^ select '[]' @@ '@# = 0'::jsquery; ?column? ---------- t (1 row) select '[]' @@ '@# < 2'::jsquery; ?column? ---------- t (1 row) select '[]' @@ '@# > 1'::jsquery; ?column? ---------- f (1 row) select '[1]' @@ '@# = 0'::jsquery; ?column? ---------- f (1 row) select '[1]' @@ '@# < 2'::jsquery; ?column? ---------- t (1 row) select '[1]' @@ '@# > 1'::jsquery; ?column? ---------- f (1 row) select '[1,2]' @@ '@# = 0'::jsquery; ?column? ---------- f (1 row) select '[1,2]' @@ '@# < 2'::jsquery; ?column? ---------- f (1 row) select '[1,2]' @@ '@# > 1'::jsquery; ?column? ---------- t (1 row) select '[1,2]' @@ '@# in (1, 2)'::jsquery; ?column? ---------- t (1 row) select '[1,2]' @@ '@# in (1, 3)'::jsquery; ?column? ---------- f (1 row) select '{"a":[1,2]}' @@ '@# in (2, 4)'::jsquery; ?column? ---------- f (1 row) select '{"a":[1,2]}' @@ 'a.@# in (2, 4)'::jsquery; ?column? ---------- t (1 row) select '{"a":[1,2]}' @@ '%.@# in (2, 4)'::jsquery; ?column? ---------- t (1 row) select '{"a":[1,2]}' @@ '*.@# in (2, 4)'::jsquery; ?column? ---------- t (1 row) select '{"a":[1,2]}' @@ '*.@# ($ = 4 or $ = 2)'::jsquery; ?column? ---------- t (1 row) select '{"a":[1,2]}' @@ '@# = 1'::jsquery; ?column? ---------- t (1 row) --ALL select 'a.*: = 4'::jsquery; jsquery ------------ "a".*: = 4 (1 row) select '%: = 4'::jsquery; jsquery --------- %: = 4 (1 row) select '#:.i = 4'::jsquery; jsquery ------------ #:."i" = 4 (1 row) select '[]' @@ '#: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- t (1 row) select '[2,3,4]' @@ '#: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- t (1 row) select '[2,3,5]' @@ '#: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- f (1 row) select '[2,3,5]' @@ '# ($ > 1 and $ < 5)'::jsquery; ?column? ---------- t (1 row) select '[2,3,"x"]' @@ '#: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- f (1 row) select '{}' @@ '%: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- t (1 row) select '{}' @@ '*: ($ is object)'::jsquery; ?column? ---------- t (1 row) select '"a"' @@ '*: is string'::jsquery; ?column? ---------- t (1 row) select '1' @@ '*: is string'::jsquery; ?column? ---------- f (1 row) select '{"a":2,"b":3,"c":4}' @@ '%: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- t (1 row) select '{"a":2,"b":3,"c":5}' @@ '%: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- f (1 row) select '{"a":2,"b":3,"c":5}' @@ '% ($ > 1 and $ < 5)'::jsquery; ?column? ---------- t (1 row) select '{"a":2,"b":3,"c":"x"}' @@ '%: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- f (1 row) select '{"a":2,"b":3,"c":4}' @@ '*: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- f (1 row) select '{"a":2,"b":3,"c":5}' @@ '*: ($ > 1 and $ < 5)'::jsquery; ?column? ---------- f (1 row) select '{"a":2,"b":3,"c":4}' @@ '*: ($ is object OR ($> 1 and $ < 5))'::jsquery; ?column? ---------- t (1 row) select '{"a":2,"b":3,"c":5}' @@ '*: ($ is object OR ($> 1 and $ < 5))'::jsquery; ?column? ---------- f (1 row) select '{"b":{"ba":3, "bb":4}}' @@ '*: ($ is object OR ($ > 1 and $ < 5))'::jsquery; ?column? ---------- t (1 row) select '{"b":{"ba":3, "bb":5}}' @@ '*: ($ is object OR ($> 1 and $ < 5))'::jsquery; ?column? ---------- f (1 row) select '{"a":{"aa":1, "ab":2}, "b":{"ba":3, "bb":4}}' @@ '*: ($ is object OR ($ > 0 and $ < 5))'::jsquery; ?column? ---------- t (1 row) select '{"a":{"aa":1, "ab":2}, "b":{"ba":3, "bb":5}}' @@ '*: ($ is object OR ($> 0 and $ < 5))'::jsquery; ?column? ---------- f (1 row) select '{"a":{"aa":1, "ab":2}, "b":{"ba":3, "bb":5}}' @@ '* ($ > 0 and $ < 5)'::jsquery; ?column? ---------- t (1 row) select '{"a":{"aa":1, "ab":2}, "b":{"ba":3, "bb":5}}' @@ '*: ($ is object OR $ is numeric)'::jsquery; ?column? ---------- t (1 row) select '{"a":{"aa":1, "ab":2}, "b":[5,6]}' @@ '*: ($ is object OR $ is numeric)'::jsquery; ?column? ---------- f (1 row) select '{"a":{"aa":1, "ab":2}, "b":[5,6]}' @@ '*: ($ is object OR $ is array OR $ is numeric)'::jsquery; ?column? ---------- t (1 row) select '{"a":{"aa":1, "ab":2}, "b":[5,6, {"c":8}]}' @@ '*: ($ is object OR $ is array OR $ is numeric)'::jsquery; ?column? ---------- t (1 row) select '{"a":{"aa":1, "ab":2}, "b":[5,6, {"c":"x"}]}' @@ '*: ($ is object OR $ is array OR $ is numeric)'::jsquery; ?column? ---------- f (1 row) select '{"a":{"aa":1, "ab":2}, "b":[5,6, {"c":null}]}' @@ '*: ($ is object OR $ is array OR $ is numeric)'::jsquery; ?column? ---------- f (1 row) select '{"a":{"aa":1}, "b":{"aa":1, "bb":2}}' @@ '%:.aa is numeric'::jsquery; ?column? ---------- t (1 row) select '{"a":{"aa":1}, "b":{"aa":true, "bb":2}}' @@ '%:.aa is numeric'::jsquery; ?column? ---------- f (1 row) select '{"a":{"aa":1}, "b":{"aa":1, "bb":2}, "aa":16}' @@ '*: (not $ is object or $.aa is numeric)'::jsquery; ?column? ---------- t (1 row) select '{"a":{"aa":1}, "b":{"aa":1, "bb":2}}' @@ '*: (not $ is object or $.aa is numeric or % is object)'::jsquery; ?column? ---------- t (1 row) select '[]' @@ '(@# > 0 and #: = 16)'::jsquery; ?column? ---------- f (1 row) select '[16]' @@ '(@# > 0 and #: = 16)'::jsquery; ?column? ---------- t (1 row) --extract entries for index scan SELECT gin_debug_query_path_value('NOT NOT NOT x(y(NOT (a=1) and NOT (b=2)) OR NOT NOT (c=3)) and z = 5'); gin_debug_query_path_value ---------------------------- AND + z = 5 , entry 0 + OR + x.y.a = 1 , entry 1 + x.y.b = 2 , entry 2 + (1 row) SELECT gin_debug_query_path_value('NOT #(x=1) and NOT *(y=1) and NOT %(z=1) '); gin_debug_query_path_value ---------------------------- NULL + (1 row) SELECT gin_debug_query_path_value('#(NOT x=1) and *(NOT y=1) and %(NOT z=1) '); gin_debug_query_path_value ---------------------------- NULL + (1 row) SELECT gin_debug_query_path_value('NOT #(NOT x=1) and NOT *(NOT y=1) and NOT %(NOT z=1) '); gin_debug_query_path_value ---------------------------- NULL + (1 row) SELECT gin_debug_query_path_value('#(x = "a" and y > 0 and y < 1 and z > 0)'); gin_debug_query_path_value ---------------------------- #.x = "a" , entry 0 + (1 row) SELECT gin_debug_query_path_value('#(x = "a" and y /*-- index */ >= 0 and y < 1 and z > 0)'); gin_debug_query_path_value ----------------------------- AND + #.x = "a" , entry 0 + #.y >= 0 , < 1 , entry 1 + (1 row) SELECT gin_debug_query_path_value('#(x /*-- noindex */ = "a" and y > 0 and y <= 1 and z /*-- index */ > 0)'); gin_debug_query_path_value ----------------------------- AND + #.y > 0 , <= 1 , entry 0 + #.z > 0 , entry 1 + (1 row) SELECT gin_debug_query_path_value('x = 1 and (y /*-- index */ > 0 and y < 1 OR z > 0)'); gin_debug_query_path_value ---------------------------- AND + x = 1 , entry 0 + OR + y > 0 , < 1 , entry 1 + z > 0 , entry 2 + (1 row) SELECT gin_debug_query_path_value('%.x = 1'); gin_debug_query_path_value ---------------------------- NULL + (1 row) SELECT gin_debug_query_path_value('*.x = "b"'); gin_debug_query_path_value ---------------------------- NULL + (1 row) SELECT gin_debug_query_path_value('x && [1,2,3]'); gin_debug_query_path_value ---------------------------- OR + x.# = 1 , entry 0 + x.# = 2 , entry 1 + x.# = 3 , entry 2 + (1 row) SELECT gin_debug_query_path_value('x @> [1,2,3]'); gin_debug_query_path_value ---------------------------- AND + x.# = 1 , entry 0 + x.# = 2 , entry 1 + x.# = 3 , entry 2 + (1 row) SELECT gin_debug_query_path_value('x <@ [1,2,3]'); gin_debug_query_path_value ---------------------------- OR + x = [] , entry 0 + x.# = 1 , entry 1 + x.# = 2 , entry 2 + x.# = 3 , entry 3 + (1 row) SELECT gin_debug_query_path_value('x = *'); gin_debug_query_path_value ---------------------------- x = * , entry 0 + (1 row) SELECT gin_debug_query_path_value('x is boolean'); gin_debug_query_path_value ---------------------------- x IS boolean , entry 0 + (1 row) SELECT gin_debug_query_path_value('x is string'); gin_debug_query_path_value ---------------------------- x IS string , entry 0 + (1 row) SELECT gin_debug_query_path_value('x is numeric'); gin_debug_query_path_value ---------------------------- x IS numeric , entry 0 + (1 row) SELECT gin_debug_query_path_value('x is array'); gin_debug_query_path_value ---------------------------- x IS array , entry 0 + (1 row) SELECT gin_debug_query_path_value('x is object'); gin_debug_query_path_value ---------------------------- x IS object , entry 0 + (1 row) SELECT gin_debug_query_path_value('#:(x=1) AND %:(y=1) AND *:(z=1)'); gin_debug_query_path_value ---------------------------- NULL + (1 row) SELECT gin_debug_query_path_value('#:(NOT x=1) AND %:(NOT y=1) AND *:(NOT z=1)'); gin_debug_query_path_value ---------------------------- NULL + (1 row) SELECT gin_debug_query_path_value('NOT #:(NOT x=1) AND NOT %:(NOT y=1) AND NOT *:(NOT z=1)'); gin_debug_query_path_value ---------------------------- #.x = 1 , entry 0 + (1 row) SELECT gin_debug_query_value_path('NOT NOT NOT x(y(NOT (a=1) and NOT (b=2)) OR NOT NOT (c=3)) and z = 5'); gin_debug_query_value_path ---------------------------- AND + z = 5 , entry 0 + OR + x.y.a = 1 , entry 1 + x.y.b = 2 , entry 2 + (1 row) SELECT gin_debug_query_value_path('NOT #(x=1) and NOT *(y=1) and NOT %(z=1) '); gin_debug_query_value_path ---------------------------- NULL + (1 row) SELECT gin_debug_query_value_path('#(NOT x=1) and *(NOT y=1) and %(NOT z=1) '); gin_debug_query_value_path ---------------------------- NULL + (1 row) SELECT gin_debug_query_value_path('NOT #(NOT x=1) and NOT *(NOT y=1) and NOT %(NOT z=1) '); gin_debug_query_value_path ---------------------------- NULL + (1 row) SELECT gin_debug_query_value_path('#(x = "a" and y > 0 and y < 1 and z > 0)'); gin_debug_query_value_path ---------------------------- #.x = "a" , entry 0 + (1 row) SELECT gin_debug_query_value_path('#(x = "a" and y /*-- index */ >= 0 and y < 1 and z > 0)'); gin_debug_query_value_path ----------------------------- AND + #.x = "a" , entry 0 + #.y >= 0 , < 1 , entry 1 + (1 row) SELECT gin_debug_query_value_path('#(x /*-- noindex */ = "a" and y > 0 and y <= 1 and z /*-- index */ > 0)'); gin_debug_query_value_path ----------------------------- AND + #.y > 0 , <= 1 , entry 0 + #.z > 0 , entry 1 + (1 row) SELECT gin_debug_query_value_path('x = 1 and (y /*-- index */ > 0 and y < 1 OR z > 0)'); gin_debug_query_value_path ---------------------------- AND + x = 1 , entry 0 + OR + y > 0 , < 1 , entry 1 + z > 0 , entry 2 + (1 row) SELECT gin_debug_query_value_path('%.x = 1'); gin_debug_query_value_path ---------------------------- %.x = 1 , entry 0 + (1 row) SELECT gin_debug_query_value_path('*.x = "b"'); gin_debug_query_value_path ---------------------------- *.x = "b" , entry 0 + (1 row) SELECT gin_debug_query_value_path('x && [1,2,3]'); gin_debug_query_value_path ---------------------------- OR + x.# = 1 , entry 0 + x.# = 2 , entry 1 + x.# = 3 , entry 2 + (1 row) SELECT gin_debug_query_value_path('x @> [1,2,3]'); gin_debug_query_value_path ---------------------------- AND + x.# = 1 , entry 0 + x.# = 2 , entry 1 + x.# = 3 , entry 2 + (1 row) SELECT gin_debug_query_value_path('x <@ [1,2,3]'); gin_debug_query_value_path ---------------------------- OR + x = [] , entry 0 + x.# = 1 , entry 1 + x.# = 2 , entry 2 + x.# = 3 , entry 3 + (1 row) SELECT gin_debug_query_value_path('x = [1,2,3]'); gin_debug_query_value_path ---------------------------- AND + x.# = 1 , entry 0 + x.# = 2 , entry 1 + x.# = 3 , entry 2 + (1 row) SELECT gin_debug_query_value_path('x = *'); gin_debug_query_value_path ---------------------------- x = * , entry 0 + (1 row) SELECT gin_debug_query_value_path('x is boolean'); gin_debug_query_value_path ---------------------------- x IS boolean , entry 0 + (1 row) SELECT gin_debug_query_value_path('x is string'); gin_debug_query_value_path ---------------------------- x IS string , entry 0 + (1 row) SELECT gin_debug_query_value_path('x is numeric'); gin_debug_query_value_path ---------------------------- x IS numeric , entry 0 + (1 row) SELECT gin_debug_query_value_path('x is array'); gin_debug_query_value_path ---------------------------- x IS array , entry 0 + (1 row) SELECT gin_debug_query_value_path('x is object'); gin_debug_query_value_path ---------------------------- x IS object , entry 0 + (1 row) SELECT gin_debug_query_value_path('#:(x=1) AND %:(y=1) AND *:(z=1)'); gin_debug_query_value_path ---------------------------- NULL + (1 row) SELECT gin_debug_query_value_path('#:(NOT x=1) AND %:(NOT y=1) AND *:(NOT z=1)'); gin_debug_query_value_path ---------------------------- NULL + (1 row) SELECT gin_debug_query_value_path('NOT #:(NOT x=1) AND NOT %:(NOT y=1) AND NOT *:(NOT z=1)'); gin_debug_query_value_path ---------------------------- AND + #.x = 1 , entry 0 + %.y = 1 , entry 1 + *.z = 1 , entry 2 + (1 row) SELECT gin_debug_query_value_path('(@# > 0 and #: = 16)'); gin_debug_query_value_path ---------------------------- NULL + (1 row) SELECT gin_debug_query_value_path('*.@# ($ = 4 or $ = 2)'); gin_debug_query_value_path ---------------------------- NULL + (1 row) ---table and index select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 0; count ------- 654 (1 row) select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 19; count ------- 13 (1 row) select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 < 19; count ------- 985 (1 row) select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 >= 19; count ------- 16 (1 row) select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 <= 19; count ------- 988 (1 row) select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 = 19; count ------- 3 (1 row) select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 16 AND (v->>'review_helpful_votes')::int4 < 20; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0'; count ------- 654 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19'; count ------- 13 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19'; count ------- 985 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19'; count ------- 16 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19'; count ------- 988 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19'; count ------- 3 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND v @@ 'review_helpful_votes < 20'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 and review_helpful_votes < 20'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 and $ < 20)'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]'; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") '; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") '; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] and product_sales_rank > 300000'; count ------- 4 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids <@ ["B00000DG0U", "B00004SQXU", "B0001XAM18", "B00000FDBU", "B00000FDBV", "B000002H2H", "B000002H6C", "B000002H5E", "B000002H97", "B000002HMH"]'; count ------- 54 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids @> ["B000002H2H", "B000002H6C"]'; count ------- 3 (1 row) select count(*) from test_jsquery where v @@ 'customer_id = null'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 'review_votes = true'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 'product_group = false'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 't = *'; count ------- 10 (1 row) select count(*) from test_jsquery where v @@ 't is boolean'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is string'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is numeric'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is array'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is object'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#: is numeric'; count ------- 51 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#: is string'; count ------- 1001 (1 row) select count(*) from test_jsquery where v @@ 'NOT similar_product_ids.#: (NOT $ = "0440180295")'; count ------- 23 (1 row) select v from test_jsquery where v @@ 'array <@ [2,3]' order by v; v ------------------- {"array": [2]} {"array": [2, 3]} (2 rows) select v from test_jsquery where v @@ 'array && [2,3]' order by v; v ---------------------- {"array": [2]} {"array": [2, 3]} {"array": [1, 2, 3]} {"array": [2, 3, 4]} {"array": [3, 4, 5]} (5 rows) select v from test_jsquery where v @@ 'array @> [2,3]' order by v; v ---------------------- {"array": [2, 3]} {"array": [1, 2, 3]} {"array": [2, 3, 4]} (3 rows) select v from test_jsquery where v @@ 'array = [2,3]' order by v; v ------------------- {"array": [2, 3]} (1 row) create index t_idx on test_jsquery using gin (v jsonb_value_path_ops); set enable_seqscan = off; explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0'; QUERY PLAN ------------------------------------------------------------------------ Aggregate -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"review_helpful_votes" > 0'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"review_helpful_votes" > 0'::jsquery) (5 rows) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0'; count ------- 654 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19'; count ------- 13 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19'; count ------- 985 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19'; count ------- 16 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19'; count ------- 988 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19'; count ------- 3 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND v @@ 'review_helpful_votes < 20'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 and review_helpful_votes < 20'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 and $ < 20)'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]'; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") '; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") '; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] and product_sales_rank > 300000'; count ------- 4 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids <@ ["B00000DG0U", "B00004SQXU", "B0001XAM18", "B00000FDBU", "B00000FDBV", "B000002H2H", "B000002H6C", "B000002H5E", "B000002H97", "B000002HMH"]'; count ------- 54 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids @> ["B000002H2H", "B000002H6C"]'; count ------- 3 (1 row) select count(*) from test_jsquery where v @@ 'customer_id = null'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 'review_votes = true'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 'product_group = false'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 't = *'; count ------- 10 (1 row) select count(*) from test_jsquery where v @@ 't is boolean'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is string'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is numeric'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is array'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is object'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#: is numeric'; count ------- 51 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#: is string'; count ------- 1001 (1 row) select count(*) from test_jsquery where v @@ 'NOT similar_product_ids.#: (NOT $ = "0440180295")'; count ------- 7 (1 row) explain (costs off) select v from test_jsquery where v @@ 'array <@ [2,3]' order by v; QUERY PLAN --------------------------------------------------------------- Sort Sort Key: v -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"array" <@ [2, 3]'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"array" <@ [2, 3]'::jsquery) (6 rows) explain (costs off) select v from test_jsquery where v @@ 'array && [2,3]' order by v; QUERY PLAN --------------------------------------------------------------- Sort Sort Key: v -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"array" && [2, 3]'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"array" && [2, 3]'::jsquery) (6 rows) explain (costs off) select v from test_jsquery where v @@ 'array @> [2,3]' order by v; QUERY PLAN --------------------------------------------------------------- Sort Sort Key: v -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"array" @> [2, 3]'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"array" @> [2, 3]'::jsquery) (6 rows) explain (costs off) select v from test_jsquery where v @@ 'array = [2,3]' order by v; QUERY PLAN -------------------------------------------------------------- Sort Sort Key: v -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"array" = [2, 3]'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"array" = [2, 3]'::jsquery) (6 rows) select v from test_jsquery where v @@ 'array <@ [2,3]' order by v; v ------------------- {"array": [2]} {"array": [2, 3]} (2 rows) select v from test_jsquery where v @@ 'array && [2,3]' order by v; v ---------------------- {"array": [2]} {"array": [2, 3]} {"array": [1, 2, 3]} {"array": [2, 3, 4]} {"array": [3, 4, 5]} (5 rows) select v from test_jsquery where v @@ 'array @> [2,3]' order by v; v ---------------------- {"array": [2, 3]} {"array": [1, 2, 3]} {"array": [2, 3, 4]} (3 rows) select v from test_jsquery where v @@ 'array = [2,3]' order by v; v ------------------- {"array": [2, 3]} (1 row) drop index t_idx; create index t_idx on test_jsquery using gin (v jsonb_path_value_ops); set enable_seqscan = off; explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0'; QUERY PLAN ------------------------------------------------------------------------ Aggregate -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"review_helpful_votes" > 0'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"review_helpful_votes" > 0'::jsquery) (5 rows) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0'; count ------- 654 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19'; count ------- 13 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19'; count ------- 985 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19'; count ------- 16 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19'; count ------- 988 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19'; count ------- 3 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND v @@ 'review_helpful_votes < 20'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 and review_helpful_votes < 20'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 and $ < 20)'; count ------- 8 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]'; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") '; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") '; count ------- 7 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] and product_sales_rank > 300000'; count ------- 4 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids <@ ["B00000DG0U", "B00004SQXU", "B0001XAM18", "B00000FDBU", "B00000FDBV", "B000002H2H", "B000002H6C", "B000002H5E", "B000002H97", "B000002HMH"]'; count ------- 54 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids @> ["B000002H2H", "B000002H6C"]'; count ------- 3 (1 row) select count(*) from test_jsquery where v @@ 'customer_id = null'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 'review_votes = true'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 'product_group = false'; count ------- 1 (1 row) select count(*) from test_jsquery where v @@ 't = *'; count ------- 10 (1 row) select count(*) from test_jsquery where v @@ 't is boolean'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is string'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is numeric'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is array'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 't is object'; count ------- 2 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#: is numeric'; count ------- 51 (1 row) select count(*) from test_jsquery where v @@ 'similar_product_ids.#: is string'; count ------- 1001 (1 row) select count(*) from test_jsquery where v @@ 'NOT similar_product_ids.#: (NOT $ = "0440180295")'; count ------- 7 (1 row) explain (costs off) select v from test_jsquery where v @@ 'array <@ [2,3]' order by v; QUERY PLAN --------------------------------------------------------------- Sort Sort Key: v -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"array" <@ [2, 3]'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"array" <@ [2, 3]'::jsquery) (6 rows) explain (costs off) select v from test_jsquery where v @@ 'array && [2,3]' order by v; QUERY PLAN --------------------------------------------------------------- Sort Sort Key: v -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"array" && [2, 3]'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"array" && [2, 3]'::jsquery) (6 rows) explain (costs off) select v from test_jsquery where v @@ 'array @> [2,3]' order by v; QUERY PLAN --------------------------------------------------------------- Sort Sort Key: v -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"array" @> [2, 3]'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"array" @> [2, 3]'::jsquery) (6 rows) explain (costs off) select v from test_jsquery where v @@ 'array = [2,3]' order by v; QUERY PLAN -------------------------------------------------------------- Sort Sort Key: v -> Bitmap Heap Scan on test_jsquery Recheck Cond: (v @@ '"array" = [2, 3]'::jsquery) -> Bitmap Index Scan on t_idx Index Cond: (v @@ '"array" = [2, 3]'::jsquery) (6 rows) select v from test_jsquery where v @@ 'array <@ [2,3]' order by v; v ------------------- {"array": [2]} {"array": [2, 3]} (2 rows) select v from test_jsquery where v @@ 'array && [2,3]' order by v; v ---------------------- {"array": [2]} {"array": [2, 3]} {"array": [1, 2, 3]} {"array": [2, 3, 4]} {"array": [3, 4, 5]} (5 rows) select v from test_jsquery where v @@ 'array @> [2,3]' order by v; v ---------------------- {"array": [2, 3]} {"array": [1, 2, 3]} {"array": [2, 3, 4]} (3 rows) select v from test_jsquery where v @@ 'array = [2,3]' order by v; v ------------------- {"array": [2, 3]} (1 row) RESET enable_seqscan;