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. 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.2050...@dunslane.net [2] http://www.postgresql.org/message-id/54ef61dd.7040...@agliodbs.com
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.
cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
