Re: jsonb concatenate operator's semantics seem questionable - Mailing list pgsql-hackers

On 17/05/15 16:04, Andrew Dunstan wrote:

On 05/16/2015 10:56 PM, Peter Geoghegan wrote:

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.

Historical note: I think it's based on the nested hstore work, not on
current hstore, but Dmitry can answer on that.

I didn't dismiss this because it was a bad idea, but because it was too
late in the process. If there is a consensus that we need to address
this now then I'm happy to reopen that, but given the recent amount of
angst about process I'm certainly not going to make such a decision
unilaterally.

Personally, I think there is plenty of room for both operations, and I
can see use cases for both. If I were designing I'd leave || as it is
now and add a + operation to do a recursive merge. I'm not sure how much
work that would be. Not huge but not trivial either.

Agreed, if you look at jquery for example, the extend() method by default behaves like our current || and you have to specify that you want deep merge if you want the behavior described by Peter. So there is definitely point for both, at this time we just support only one of them, that's all.


--
 Petr Jelinek                  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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