Thread: Are we sufficiently clear that jsonb containment is nested?
I worry that "8.14.3. jsonb Containment and Existence" is not
sufficiently clear in explaining that jsonb containment is nested.
I've seen anecdata suggesting that this is unclear to users. We do
say:
"""
The general principle is that the contained object must match the
containing object as to structure and data contents, possibly after
discarding some non-matching array elements or object key/value pairs
from the containing object.
"""
I think that we could still do with an example showing *nested*
containment, where many non-matching elements/pairs at each of several
nesting levels are discarded. This could be back-patched to 9.4.
Something roughly like the delicious sample data, where queries like
the following are possible and useful:
postgres=# select jsonb_pretty(doc) from delicious where doc @>
'{"tags":[{"term":"Florence" }, {"term":"food"} ] }' limit 1;
jsonb_pretty
-----------------------------------------------------------------------------------------------------------------{
+ "id": "http://delicious.com/url/5f05d61a6e8519e9c9c8c557216375da#Avrami",
+ "link":
"http://www.foodnetwork.com/recipes/tyler-florence/the-ultimate-lasagna-recipe/index.html", + "tags": [
+ { + "term": "Lasagna",
+ "label": null, + "scheme":
"http://delicious.com/Avrami/" + },
+
*** SNIP *** + "title": "The Ultimate Lasagna Recipe : Tyler Florence : Food
Network", + "author": "Avrami", +
"source":{ + }, + "updated": "Fri,
11Sep 2009 17:09:20 +0000", + "comments":
"http://delicious.com/url/5f05d61a6e8519e9c9c8c557216375da", + "guidislink": false,
+ "title_detail": { + "base":
"http://feeds.delicious.com/v2/rss/recent?min=1&count=100", + "type": "text/plain",
+ "value": "The Ultimate Lasagna Recipe : Tyler Florence : Food
Network", + "language": null + },
+ "wfw_commentrss":
"http://feeds.delicious.com/v2/rss/url/5f05d61a6e8519e9c9c8c557216375da" +}
(1 row)
Obviously a real doc-patch example would have to be more worked out
and clearer than what I show here. My immediate concern is whether
users appreciate that jsonb is capable of this kind of complex, nested
containment-driven querying. I do not recall ever seeing an example
like this in the wild, which is where this concern comes from. It
would be a shame if they were working around a non-existent
limitation, especially given that this kind of thing can work
reasonably effectively with the jsonb_path_ops opclass.
Opinions?
--
Peter Geoghegan
On Wed, Jun 24, 2015 at 5:53 PM, Peter Geoghegan <pg@heroku.com> wrote: > I worry that "8.14.3. jsonb Containment and Existence" is not > sufficiently clear in explaining that jsonb containment is nested. > I've seen anecdata suggesting that this is unclear to users. We do > say: > > """ > The general principle is that the contained object must match the > containing object as to structure and data contents, possibly after > discarding some non-matching array elements or object key/value pairs > from the containing object. > """ > > I think that we could still do with an example showing *nested* > containment, where many non-matching elements/pairs at each of several > nesting levels are discarded. This could be back-patched to 9.4. > Something roughly like the delicious sample data, where queries like > the following are possible and useful: I would be fine with adding a *compact* example of this kind to the table that begins section 8.14.3. I probably would not back-patch it, because the absence of that example is not an error in the documentation, but I will not complain if someone else does. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, Jun 26, 2015 at 8:05 AM, Robert Haas <robertmhaas@gmail.com> wrote: > I would be fine with adding a *compact* example of this kind to the > table that begins section 8.14.3. I probably would not back-patch it, > because the absence of that example is not an error in the > documentation, but I will not complain if someone else does. How about the attached? It's a compact refinement of my original example of more complicated jsonb containment. I still think it would be a good idea to go back to 9.4. I have reason to believe that people are getting confused on this point. -- Peter Geoghegan
Attachment
Peter Geoghegan <pg@heroku.com> writes:
> On Fri, Jun 26, 2015 at 8:05 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> I would be fine with adding a *compact* example of this kind to the
>> table that begins section 8.14.3. I probably would not back-patch it,
>> because the absence of that example is not an error in the
>> documentation, but I will not complain if someone else does.
> How about the attached? It's a compact refinement of my original
> example of more complicated jsonb containment.
You seem to have injected unrelated text into the middle of a discussion.
I think what you added is fine, but not where you put it; it would make
more sense as a standalone para.
I think the existing text is largely my fault, so I'll do something with
this.
> I still think it would be a good idea to go back to 9.4. I have reason
> to believe that people are getting confused on this point.
You didn't present evidence backing that up, but I agree that
clarification is a sufficient reason to back-patch doc changes.
regards, tom lane
On Thu, Oct 29, 2015 at 1:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I think the existing text is largely my fault, so I'll do something with > this. Good. Thanks. >> I still think it would be a good idea to go back to 9.4. I have reason >> to believe that people are getting confused on this point. > > You didn't present evidence backing that up, but I agree that > clarification is a sufficient reason to back-patch doc changes. It's difficult to provide evidence for the existence of a perception among users when it's a perception that a quasi-reasonable limitation exists. The fact that jsonb_path_ops can make indexing complex jsonb documents practical is fairly novel, so I'm not surprised that users seem to imagine that containment does not work in a nested fashion. "Existence" (the ? operator) actually has this limitation. -- Peter Geoghegan
Peter Geoghegan <pg@heroku.com> writes:
> On Thu, Oct 29, 2015 at 1:16 PM, Tom Lane <tgl@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 @> '["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 @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';
+ </programlisting>
+ One could accomplish the same thing with, say,
+ <programlisting>
+ SELECT doc->'site_name' FROM websites
+ WHERE doc->'tags' @> '[{"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
On Thu, Oct 29, 2015 at 2:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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? Robert seemed to want to keep the example short, which I took on board, but I myself think that your more worked out treatment is better. I think this revision makes my point very well. I recommend committing it. -- Peter Geoghegan
Peter Geoghegan <pg@heroku.com> writes:
> Robert seemed to want to keep the example short, which I took on
> board, but I myself think that your more worked out treatment is
> better. I think this revision makes my point very well. I recommend
> committing it.
After further thought I realized that part of the point you'd been
making was that people might fail to distinguish the behaviors of
containment and existence operators in this regard. So I think the
example needs to make that point explicitly. I whacked it around
a bit more and committed it.
regards, tom lane
On Thu, Oct 29, 2015 at 4:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > After further thought I realized that part of the point you'd been > making was that people might fail to distinguish the behaviors of > containment and existence operators in this regard. So I think the > example needs to make that point explicitly. I whacked it around > a bit more and committed it. Looks good. -- Peter Geoghegan