jsonb concatenate operator's semantics seem questionable

Поиск
Список
Период
Сортировка
От Peter Geoghegan
Тема jsonb concatenate operator's semantics seem questionable
Дата
Msg-id CAM3SWZRcSp1dS0o5gXeepO7k4QxjjO+h_NsRnCA=mx-OEJ8vYQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: jsonb concatenate operator's semantics seem questionable  (Robert Haas <robertmhaas@gmail.com>)
Re: jsonb concatenate operator's semantics seem questionable  (Andrew Dunstan <andrew@dunslane.net>)
Re: jsonb concatenate operator's semantics seem questionable  ("Ilya I. Ashchepkov" <koctep@gmail.com>)
Список pgsql-hackers
Another thing that I noticed about the new jsonb stuff is that the
concatenate operator is based on the hstore one. This works as
expected:

postgres=# select '{"a":1}'::jsonb || '{"a":2}';
?column?
----------
 {"a": 2}
(1 row)

However, the nesting doesn't "match up" -- containers are not merged
beyond the least-nested level:

postgres=# select '{"a":{"nested":1}}'::jsonb || '{"a":{"also nested":2}}';
         ?column?
---------------------------
 {"a": {"also nested": 2}}
(1 row)

This feels wrong to me. When jsonb was initially introduced, we took
inspiration for the *containment* ("operator @> jsonb") semantics from
hstore, but since jsonb is nested it worked in a nested fashion. At
the top level and with no nested containers there was no real
difference, but we had to consider the behavior of more nested levels
carefully (the containment operator is clearly the most important
jsonb operator). I had envisaged that with the concatenation of jsonb,
concatenation would similarly behave in a nested fashion. Under this
scheme, the above query would perform nested concatenation as follows:

postgres=# select '{"a":{"nested":1}}'::jsonb || '{"a":{"also
nested":2}}'; -- does not match actual current behavior
         ?column?
---------------------------
 {"a": {"nested":1, "also nested": 2}}
(1 row)

Now, I think it's good that the minus operator ("operator - text" and
friends) discussed on the nearby thread accepts a text (or int)
argument and remove string elements/pairs at the top level only. This
works exactly the same as existence (I happen to think that removing
elements/pairs at a nested level is likely to be more trouble than
it's worth, and so I don't really like the new "jsonb - text[]"
operator much, because it accepts a Postgres (not JSON) array of texts
that constitute a path, which feels odd). So I have no issue with at
least the plain minus operators' semantics. But I think that the
concatenate operator's current semantics are significantly less useful
than they could be, and are not consistent with the overall design of
jsonb.

I'm particularly concerned about a table containing many homogeneously
structured, deeply nested jsonb datums (think of the delicious URLs
dataset that jsonb was originally tested using for a good example of
that -- this is quite representative of how people use jsonb in the
real world). It would be almost impossible to perform insert-or-update
type operations to these deeply nested elements using hstore style
concatenation. You'd almost invariably end up removing a bunch of
irrelevant nested values of the documents, when you only intended to
update one deeply nested value.

Looking back at the discussion of the new jsonb stuff, a concern was
raised along these lines by Ilya Ashchepkov [1], but this was
dismissed. I feel pretty strongly that this should be revisited. I'm
willing to concede that we might not want to always merge containers
that are found in the same position during concatenation, but I think
it's more likely that we do. As with containment, my sense is that
there should be nothing special about the nesting level -- it should
not influence whether we merge rather than overwrite the operator's
lhs container (with or into the rhs container). Not everyone will
agree with this [2].

I'm sorry that I didn't get to this sooner, but I was rather busy when
it was being discussed.

[1] http://www.postgresql.org/message-id/55006879.2050601@dunslane.net
[2] http://www.postgresql.org/message-id/54EF61DD.7040208@agliodbs.com
--
Peter Geoghegan



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

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: upper planner path-ification
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: hstore_plpython regression test does not work on Python 3