Subject: JSONB operator clarification

From: Bruce Momjian <[email-masked]>
Date: Mon, 5 Oct 2015 20:19:26 -0400

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  <[email-masked]>        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 ****
         <row>
          <entry><literal>@&gt;</literal></entry>
          <entry><type>jsonb</type></entry>
!         <entry>Does the left JSON value contain within it the right value?</entry>
          <entry><literal>'{"a":1, "b":2}'::jsonb &#64;&gt; '{"b":2}'::jsonb</literal></entry>
         </row>
         <row>
          <entry><literal>&lt;@</literal></entry>
          <entry><type>jsonb</type></entry>
!         <entry>Is the left JSON value contained within the right value?</entry>
          <entry><literal>'{"b":2}'::jsonb &lt;@ '{"a":1, "b":2}'::jsonb</literal></entry>
         </row>
         <row>
          <entry><literal>?</literal></entry>
          <entry><type>text</type></entry>
!         <entry>Does the key/element <emphasis>string</emphasis> exist within
!         the JSON value?</entry>
          <entry><literal>'{"a":1, "b":2}'::jsonb ? 'b'</literal></entry>
         </row>
         <row>
          <entry><literal>?|</literal></entry>
          <entry><type>text[]</type></entry>
!         <entry>Do any of these key/element <emphasis>strings</emphasis> exist?</entry>
          <entry><literal>'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']</literal></entry>
         </row>
         <row>
          <entry><literal>?&amp;</literal></entry>
          <entry><type>text[]</type></entry>
!         <entry>Do all of these key/element <emphasis>strings</emphasis> exist?</entry>
          <entry><literal>'["a", "b"]'::jsonb ?&amp; array['a', 'b']</literal></entry>
         </row>
         <row>
--- 10331,10366 ----
         <row>
          <entry><literal>@&gt;</literal></entry>
          <entry><type>jsonb</type></entry>
!         <entry>Does the left JSON value contain the right JSON
!         path/value entries at the top level?</entry>
          <entry><literal>'{"a":1, "b":2}'::jsonb &#64;&gt; '{"b":2}'::jsonb</literal></entry>
         </row>
         <row>
          <entry><literal>&lt;@</literal></entry>
          <entry><type>jsonb</type></entry>
!         <entry>Are the left JSON path/value entries contained at the top level within
!         the right JSON value?</entry>
          <entry><literal>'{"b":2}'::jsonb &lt;@ '{"a":1, "b":2}'::jsonb</literal></entry>
         </row>
         <row>
          <entry><literal>?</literal></entry>
          <entry><type>text</type></entry>
!         <entry>Does the <emphasis>string</emphasis> exist as a top-level
!         key within the JSON value?</entry>
          <entry><literal>'{"a":1, "b":2}'::jsonb ? 'b'</literal></entry>
         </row>
         <row>
          <entry><literal>?|</literal></entry>
          <entry><type>text[]</type></entry>
!         <entry>Do any of these array <emphasis>strings</emphasis>
!         exist as top-level keys?</entry>
          <entry><literal>'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']</literal></entry>
         </row>
         <row>
          <entry><literal>?&amp;</literal></entry>
          <entry><type>text[]</type></entry>
!         <entry>Do all of these array <emphasis>strings</emphasis> exist
!         as top-level keys?</entry>
          <entry><literal>'["a", "b"]'::jsonb ?&amp; array['a', 'b']</literal></entry>
         </row>
         <row>
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 ****
    </para>
    <para>
      The default GIN operator class for <type>jsonb</> supports queries with
!     the <literal>@&gt;</>, <literal>?</>, <literal>?&amp;</>
!     and <literal>?|</> operators.
      (For details of the semantics that these operators
      implement, see <xref linkend="functions-jsonb-op-table">.)
      An example of creating an index with this operator class is:
--- 369,377 ----
    </para>
    <para>
      The default GIN operator class for <type>jsonb</> supports queries with
!     top-level key-exists operators <literal>?</>, <literal>?&amp;</>
!     and <literal>?|</> operators and path/value-exists operator
!     <literal>@&gt;</>.
      (For details of the semantics that these operators
      implement, see <xref linkend="functions-jsonb-op-table">.)
      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 jsonb_exists_all contsel contjoinsel ));
! DESCR("exists all");
  DATA(insert OID = 3250 (  "<@"	   PGNSP PGUID b f f 3802 3802 16 3246 0 jsonb_contained contsel contjoinsel ));
  DESCR("is contained by");
  DATA(insert OID = 3284 (  "||"	   PGNSP PGUID b f f 3802 3802 3802 0 0 jsonb_concat - - ));
--- 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("key exists");
  DATA(insert OID = 3248 (  "?|"	   PGNSP PGUID b f f 3802 1009 16 0 0 jsonb_exists_any contsel contjoinsel ));
! DESCR("any key exists");
  DATA(insert OID = 3249 (  "?&"	   PGNSP PGUID b f f 3802 1009 16 0 0 jsonb_exists_all contsel contjoinsel ));
! DESCR("all keys exist");
  DATA(insert OID = 3250 (  "<@"	   PGNSP PGUID b f f 3802 3802 16 3246 0 jsonb_contained contsel contjoinsel ));
  DESCR("is contained by");
  DATA(insert OID = 3284 (  "||"	   PGNSP PGUID b f f 3802 3802 3802 0 0 jsonb_concat - - ));

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 7 Oct 2015 09:07:35 -0400

> anywhere inside the document:

Applied and backpatched to 9.5.  No catalog bump as the catalog changes
are only for SQL comments.