Re: [HACKERS] Are we sufficiently clear that jsonb containment is nested?

[Tom Lane]

Peter Geoghegan <p...@heroku.com> writes:
> On Thu, Oct 29, 2015 at 1:16 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>> I think the existing text is largely my fault, so I'll do something with
>> this.

> Good. Thanks.

After studying the proposed patch a bit more, I still think the example
is good, but the added text doesn't do much to explain your point.  If
I get what your point is, which maybe I don't, I think the attached might
clarify it better.  What do you think of this version?

                        regards, tom lane


diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 118fb35..b2efc80 100644
*** a/doc/src/sgml/json.sgml
--- b/doc/src/sgml/json.sgml
*************** SELECT '"bar"'::jsonb @&gt; '["bar"]'::j
*** 319,324 ****
--- 319,346 ----
  </programlisting>
  
    <para>
+    Keep in mind that containment is <emphasis>nested</>; an appropriate
+    query can skip explicit selection of sub-objects.  As an example, suppose
+    that our <structfield>doc</> column contains objects at the top level,
+    with all or most objects containing <literal>tags</> fields that contain
+    arrays of sub-objects.  This query finds entries in which sub-objects
+    containing both <literal>"term":"paris"</> and <literal>"term":"food"</>
+    appear, ignoring any cases where such keys appear outside
+    the <literal>tags</> array:
+ <programlisting>
+ -- Return "site_name" where at least the terms "paris" and "food" appear:
+ SELECT doc->'site_name' FROM websites
+   WHERE doc @&gt; '{"tags":[{"term":"paris"}, {"term":"food"}]}';
+ </programlisting>
+    One could accomplish the same thing with, say,
+ <programlisting>
+ SELECT doc->'site_name' FROM websites
+   WHERE doc->'tags' @&gt; '[{"term":"paris"}, {"term":"food"}]';
+ </programlisting>
+    but that approach is less flexible, and often less efficient as well.
+   </para>
+ 
+   <para>
      <type>jsonb</> also has an <firstterm>existence</> operator, which is
      a variation on the theme of containment: it tests whether a string
      (given as a <type>text</> value) appears as an object key or array

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers