Hello! [3] First of all few words about concatenation of jsonb values in my mind. Jsonb values concatenation result may follow this rules: 1) array of both values if both are scalars 2) concatenated array if both are arrays 3) prepended or appended array if only one is array 4) recursive concatenated jsonb-object if both is objects
[4] When we are merging two jsonb-objects and we have a key in both we can: 1) use value from last jsonb-object 2) concatenate this values using rules from [3] [5] Also I want to be able to do the following: # select JSONB_MERGE_FUN(SMTHNG, '{"a":{"b":1,"c":1}}'::jsonb, '{"a":{"b":2,"c":2}}'::jsonb); ?column? ------------------------- {"a":{"b":[1,2],"c":2}} I'm thinking about SMTHNG as a function, that receive: 1) array of keys - where merging "cursor" is in jsonb objects 2) types of jsonb objects at this cursor (null/scalar/array/object) Returned value determinates behaviour that will be used to apply rules from [4]. Current function implements the following algorithm (for objects): 1) use value from last object if keys array length is not 0 My implementation: 1) use value from last object if type is scalar or array 2) concatenate values if both are objects At this moment I know how implement something like function from [5] on C, but I think it will be not very useful for developers. I think it will be useful if it may be implemented like aggregate. Any thoughts? On Sun, May 17, 2015 at 2:56 AM, Peter Geoghegan <p...@heroku.com> 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 > -- > Peter Geoghegan -- С уважением, Ащепков Илья koc...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers