Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZjFyf-0002Ae-Fd for pgsql-docs@arkaria.postgresql.org; Tue, 06 Oct 2015 00:19:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZjFye-0005NO-M1 for pgsql-docs@arkaria.postgresql.org; Tue, 06 Oct 2015 00:19:32 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZjFyc-0005Mn-Mm for pgsql-docs@postgresql.org; Tue, 06 Oct 2015 00:19:30 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZjFyZ-0005X2-IS for pgsql-docs@postgresql.org; Tue, 06 Oct 2015 00:19:29 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1ZjFyY-0008RI-BB for pgsql-docs@postgresql.org; Mon, 05 Oct 2015 20:19:26 -0400 Date: Mon, 5 Oct 2015 20:19:26 -0400 From: Bruce Momjian To: PostgreSQL-documentation Subject: JSONB operator clarification Message-ID: <20151006001926.GA9634@momjian.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="MGYHOYXEY6WxJCY8" Content-Disposition: inline User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org --MGYHOYXEY6WxJCY8 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 + --MGYHOYXEY6WxJCY8 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="json.diff" 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 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 - - )); --MGYHOYXEY6WxJCY8 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs --MGYHOYXEY6WxJCY8--