In studying our JSONB operators, I was confused about what they do based
on the docs. I found that "contain within" means "contain the
path/value", i.e. it has to match the path from the top level, not just
anywhere inside the document:
SELECT '{"a":1, "c" : {"b":2}}'::jsonb @> '{"a":1}'::jsonb;
?column?
--
t
SELECT '{"a":1, "c" : {"b":2}}'::jsonb @> '{"b":2}'::jsonb;
?column?
--
f
You can also specify only the top part of the path:
SELECT '{"a":1, "c" : {"b":2}}'::jsonb @> '{"c":{}}'::jsonb;
?column?
--
t
SELECT '{"a":1, "c" : {"b":2}}'::jsonb @> '{"d":{}}'::jsonb;
?column?
--
f
I also found that "key/element string exist" really means "string exist
as a top-level key", e.g.
SELECT '{"a":1, "c" : {"b":2}}'::jsonb ? 'a';
?column?
--
t
SELECT '{"a":1, "c" : {"b":2}}'::jsonb ? 'b';
?column?
--
f
The attached doc patch and SQL comment update improves this.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 897ed64..4ddd01c
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** table2-mapping
*** 10331,10362
@>
jsonb
! Does the left JSON value contain within it the right value?
'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@
jsonb
! Is the left JSON value contained within the right value?
'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?
text
! Does the key/element string exist within
! the JSON value?
'{"a":1, "b":2}'::jsonb ? 'b'
?|
text[]
! Do any of these key/element strings exist?
'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
?&
text[]
! Do all of these key/element strings exist?
'["a", "b"]'::jsonb ?& array['a', 'b']
--- 10331,10366
@>
jsonb
! Does the left JSON value contain the right JSON
! path/value entries at the top level?
'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@
jsonb
! Are the left JSON path/value entries contained at the top level within
! the right JSON value?
'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?
text
! Does the string exist as a top-level
! key within the JSON value?
'{"a":1, "b":2}'::jsonb ? 'b'
?|
text[]
! Do any of these array strings
! exist as top-level keys?
'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
?&
text[]
! Do all of these array strings exist
! as top-level keys?
'["a", "b"]'::jsonb ?& array['a', 'b']
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
new file mode 100644
index 1e78558..b4b390b
*** a/doc/src/sgml/json.sgml
--- b/doc/src/sgml/json.sgml
*** SELECT '"foo"'::jsonb ? 'foo';
*** 369,376
The default GIN operator class for jsonb supports queries with
! the @>, ?, ?&
! and ?| operators.
(For details of the semantics that these operators
implement, see .)
An example of creating an index with this operator class is:
--- 369,377
The default GIN operator class for jsonb supports queries with
! top-level key-exists operators ?, ?&
! and ?| operators and path/value-exists operator
! @>.
(For details of the semantics that these operators
implement, see .)
An example of creating an index with this operator class is:
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
new file mode 100644
index 26c9d4e..eec9c60
*** a/src/include/catalog/pg_operator.h
--- b/src/include/catalog/pg_operator.h
*** DESCR("greater than or equal");
*** 1806,1816
DATA(insert OID = 3246 ( "@>" PGNSP PGUID b f f 3802 3802 16 3250 0 jsonb_contains contsel contjoinsel ));
DESCR("contains");
DATA(insert OID = 3247 ( "?" PGNSP PGUID b f f 3802 25 16 0 0 jsonb_exists contsel contjoinsel ));
! DESCR("exists");
DATA(insert OID = 3248 ( "?|" PGNSP PGUID b f f 3802 1009 16 0 0 jsonb_exists_any contsel contjoinsel ));
! DESCR("exists any");
DATA(insert OID = 3249 ( "?&" PGNSP PGUID b f f 3802 1009 16 0 0
