Are we sufficiently clear that jsonb containment is nested?

Поиск
Список
Период
Сортировка
От Peter Geoghegan
Тема Are we sufficiently clear that jsonb containment is nested?
Дата
Msg-id CAM3SWZTBCokR3T-WOW+KdAasvUp=ntG+mQ__z72Ew74-4W0b3w@mail.gmail.com
обсуждение исходный текст
Ответы Re: Are we sufficiently clear that jsonb containment is nested?
Список 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,
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



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: Oh, this is embarrassing: init file logic is still broken
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: 9.5 release notes