Are we sufficiently clear that jsonb containment is nested?

From: Peter Geoghegan <pg(at)heroku(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Are we sufficiently clear that jsonb containment is nested?
Date: 2015-06-24 21:53:18
Message-ID: CAM3SWZTBCokR3T-WOW+KdAasvUp=ntG+mQ__z72Ew74-4W0b3w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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, 11 Sep 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2015-06-24 22:47:09 Re: 9.5 release notes
Previous Message Josh Berkus 2015-06-24 21:52:48 Re: Oh, this is embarrassing: init file logic is still broken