jsonb concatenate operator's semantics seem questionable

On May 16, 2015, at 10:56 PM, Peter Geoghegan <pg@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)

I'm not as much of a JSON user as some here, for sure, but for what it's worth my intuition here matches yours.

...Robert

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

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] /messages/by-id/55006879.2050601@dunslane.net
[2] /messages/by-id/54EF61DD.7040208@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

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

Historical note: I think it's based on the nested hstore work, not on

current hstore, but Dmitry can answer on that.

Yes, you're right.
And I agree with thoughts above, that both concatenation modes ("simple"
and "deep") definitely can be useful. I can try to figure out how much work
that would be to modify the IteratorConcat function (or adapt Ilya's
solution)

On 17 May 2015 at 21:16, Petr Jelinek <petr@2ndquadrant.com> wrote:

On Sun, May 17, 2015 at 7:16 AM, Petr Jelinek <petr@2ndquadrant.com> wrote:

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.

The difference is that with jquery's extend(), you can easily
subscript the JSON document you're extending so that you do so for the
right nested object (without merging any containers the target
contains, but rather overwriting them with any of the source's
containers -- makes sense when you're explicit about the nesting level
like that). With jsonb, however, we're usually stuck with having to
write an SQL expression that evaluates to the final jsonb document
that we want (for UPDATE targetlist assignment, typically).

This is an enormous difference. We still don't have a way to update a
nested object's single field that I can see, and nested objects are
absolutely commonplace for the "document database" use case. So I
don't accept that this is a matter of individual preference or taste.
It's a big, practical distinction.

--
Peter Geoghegan

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

On Sun, May 17, 2015 at 8:37 AM, Dmitry Dolgov <9erthalion6@gmail.com> wrote:

And I agree with thoughts above, that both concatenation modes ("simple" and
"deep") definitely can be useful. I can try to figure out how much work that
would be to modify the IteratorConcat function (or adapt Ilya's solution)

I tend to think of it like this: jsonb more or less supports two use cases.

Firstly, it supports the hstore use case, with a heterogeneous object
stored in each row -- a hodge-podge of different attributes, which can
be used to do something EAV-like, where there are application end user
defined attributes, say. This is unlikely to involve any nesting,
because we're only storing attributes of one entity (the row). This
isn't the use of jsonb that people got excited about, and I think it's
less important, although it does matter. The existence operator (which
operates at the least nested level) is firmly about this use case. And
for that matter, so is the new remove capability/minus operator thing
(which also operates at the least nested level). Fine.

The second use case is the "document database" use case, which is
where jsonb is really compelling. Think of storing more or less fixed
structure documents from a third party web API. Containment works in a
nested fashion in support of that. And as I pointed out, not having
the concatenate operator work in a nested fashion hobbles this use
case. How are users supposed to write an SQL query that update's a
single field in a nested object? That's obviously what they expect
from this.

I think it's misguided to make the concatenate operator target the
hstore use case - if you have that use case, you're unlikely to have
any nesting by convention anyway, and so it doesn't matter to you.
Besides which, as I said, the "document database" use case is the one
most users actually care about these days.

--
Peter Geoghegan

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

On 05/17/2015 05:56 PM, Peter Geoghegan wrote:

On Sun, May 17, 2015 at 8:37 AM, Dmitry Dolgov <9erthalion6@gmail.com> wrote:

And I agree with thoughts above, that both concatenation modes ("simple" and
"deep") definitely can be useful. I can try to figure out how much work that
would be to modify the IteratorConcat function (or adapt Ilya's solution)

I tend to think of it like this: jsonb more or less supports two use cases.

Firstly, it supports the hstore use case, with a heterogeneous object
stored in each row -- a hodge-podge of different attributes, which can
be used to do something EAV-like, where there are application end user
defined attributes, say. This is unlikely to involve any nesting,
because we're only storing attributes of one entity (the row). This
isn't the use of jsonb that people got excited about, and I think it's
less important, although it does matter. The existence operator (which
operates at the least nested level) is firmly about this use case. And
for that matter, so is the new remove capability/minus operator thing
(which also operates at the least nested level). Fine.

The second use case is the "document database" use case, which is
where jsonb is really compelling. Think of storing more or less fixed
structure documents from a third party web API. Containment works in a
nested fashion in support of that. And as I pointed out, not having
the concatenate operator work in a nested fashion hobbles this use
case. How are users supposed to write an SQL query that update's a
single field in a nested object? That's obviously what they expect
from this.

I think it's misguided to make the concatenate operator target the
hstore use case - if you have that use case, you're unlikely to have
any nesting by convention anyway, and so it doesn't matter to you.
Besides which, as I said, the "document database" use case is the one
most users actually care about these days.

Peter,

Nobody is arguing that what you want isn't desirable. It just happens to
be what we don't have. I get your disappointment, but you actually had a
long time after the original patch was published to make your case. When
I suggested 2 months ago to someone else that it was really too late to
be adding this feature, nobody, including you, disagreed.

So what exactly do you want me or anybody else to do now, two days
*after* we declared (not without pain) feature freeze?

If there is a consensus that what you want is so important that we need
to implement the new behaviour at this late stage, I'm happy to spend
time on it if there's a patch forthcoming. I might add that this should
be an additional behaviour, since as Petr points out there is some
validity to the current behviour.

If not, a function and operator can almost certainly be created with
this behaviour as an extension for those who really need it in 9.5. I'm
sure Dmitry will be happy to work on that.

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

On Sun, May 17, 2015 at 3:22 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

So what exactly do you want me or anybody else to do now, two days *after*
we declared (not without pain) feature freeze?

As much as I'd like to just fix the concatenate operator, I really
don't want to be the person that adds additional delay to stabilizing
9.5.

If there is a consensus that what you want is so important that we need to
implement the new behaviour at this late stage, I'm happy to spend time on
it if there's a patch forthcoming. I might add that this should be an
additional behaviour, since as Petr points out there is some validity to the
current behviour.

The current behavior does not seem acceptable for the concatenate
operator ("operator || jsonb"). If we can't fix it as the concatenate
operator, I think we should change it to be "operator + jsonb" or
something. It should be explained and understood as an operator that
(like the original existence operator "operator ? text", but unlike
the original containment operator "operator @> jsonb") exists only to
support the less common hstore-style use case. It should also not be
referred to as offering concatenation, but something more specialized
than that. I'm sorry, but as things stand I don't think that the
concatenation behavior makes sense as the general purpose jsonb
concatenate operator.

I'll go with the consensus, but obviously I feel pretty strongly that
we have the behavior of "operator || jsonb" wrong. We should at the
very least work towards a future version where there is a "operator ||
jsonb" that does the right thing. I don't even like the idea of having
this as an "operator + jsonb" operator, though, because it introduces
an operator that is concerned with the hstore-style use case that I
described before, and yet accepts a jsonb datum on the rhs.

Again, I'm sorry that I brought this up late, and I hope that this
doesn't seem capricious. I just happen to sincerely feel that the
current state of "operator || jsonb" leads us in the wrong long-term
direction. Let's hear what other people think, though.

--
Peter Geoghegan

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

On May 17, 2015, at 8:38 PM, Peter Geoghegan <pg@heroku.com> wrote:

The current behavior does not seem acceptable for the concatenate
operator ("operator || jsonb").

I don't agree. It seems pretty clear to me after reading the new posts that the behavior is not an oversight, and that's enough for me to say that we should leave this alone.

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

On Sun, May 17, 2015 at 5:46 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On May 17, 2015, at 8:38 PM, Peter Geoghegan <pg@heroku.com> wrote:

The current behavior does not seem acceptable for the concatenate
operator ("operator || jsonb").

I don't agree. It seems pretty clear to me after reading the new posts that the behavior is not an oversight, and that's enough for me to say that we should leave this alone.

I've said what I wanted to say. As long as the community is
comfortable with the reality that this concatenate operator really
isn't useful for assignment within UPDATEs for most jsonb users, then
I can leave it at that. I think that this concatenate operator may
have been informally promoted as the thing that made it possible to do
jsonb UPDATEs in a declarative fashion, but as things stand that
really isn't true.

If nothing changes, let's not make the mistake of going on to
*formally* promote this concatenate operator as offering the ability
to do jsonb UPDATEs in a declarative fashion, because that would be
quite misleading.

--
Peter Geoghegan

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

On 05/17/2015 05:46 PM, Robert Haas wrote:

On May 17, 2015, at 8:38 PM, Peter Geoghegan <pg@heroku.com> wrote:

The current behavior does not seem acceptable for the concatenate
operator ("operator || jsonb").

I don't agree. It seems pretty clear to me after reading the new posts that the behavior is not an oversight, and that's enough for me to say that we should leave this alone.

Is there a particular reason why "+" makes more sense as "shallow
concatination" and "||" makes more sense as "deep concatination"? Like,
something in JS or other client languages which would make that
preference make more sense to users?

While I hate last-minute changes in general, once we have this
functionality as || we won't be able to swap operators later if we
decide that deep concatination should have been ||. So I want to be
clear on why users will prefer that to + .

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

On Sun, May 17, 2015 at 8:41 PM, Josh Berkus <josh@agliodbs.com> wrote:

Is there a particular reason why "+" makes more sense as "shallow
concatination" and "||" makes more sense as "deep concatination"? Like,
something in JS or other client languages which would make that
preference make more sense to users?

While I hate last-minute changes in general, once we have this
functionality as || we won't be able to swap operators later if we
decide that deep concatination should have been ||. So I want to be
clear on why users will prefer that to + .

This guy is talking about the concatenation operator in hstore:
https://twitter.com/toolmantim/status/589348855302344705

I don't want to present this operator as being the equivalent for
jsonb (the thing you use for assignment). I wish it was, but it just
isn't, as long as it treats the first nesting level as special. jsonb
is all about nesting in general, and its concatenate operator must
reflect this. It wouldn't be much use if "operator @> jsonb" didn't
care about nesting either, but it does (unlike hstore's equivalent,
because hstore doesn't nest).

I don't think that (say) an "operator + jsonb" ought to be called a
concatenation operator at all. The idea is to distance what we have
here from the idea of an hstore concatenate operator, and to encourage
the understanding that it has only a specialized use. I think that the
danger of someone making the wrong assumption about the new "operator
|| jsonb" is very real (I think that the reverse wouldn't be true,
though, if concatenation worked in a nested fashion -- that wouldn't
bother users that had non-nested jsonb documents).

As I said, I don't think that my preference for deep concatenation is
a matter of taste. I think that shallow concatenation is fundamentally
and objectively at odds with what jsonb is supposed to be (as long as
concatenation is the way "nested assignment" works, which is what
users have been taught to think).
--
Peter Geoghegan

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

18.05.2015, 06:41, Josh Berkus kirjoitti:

On 05/17/2015 05:46 PM, Robert Haas wrote:

On May 17, 2015, at 8:38 PM, Peter Geoghegan <pg@heroku.com> wrote:

The current behavior does not seem acceptable for the concatenate
operator ("operator || jsonb").

I don't agree. It seems pretty clear to me after reading the new posts that the behavior is not an oversight, and that's enough for me to say that we should leave this alone.

Is there a particular reason why "+" makes more sense as "shallow
concatination" and "||" makes more sense as "deep concatination"? Like,
something in JS or other client languages which would make that
preference make more sense to users?

While I hate last-minute changes in general, once we have this
functionality as || we won't be able to swap operators later if we
decide that deep concatination should have been ||. So I want to be
clear on why users will prefer that to + .

Both operations (shallow and deep merge) are useful and needed in many
applications but I've found the shallow merge to be more useful in the
"generic" use case; the deep merge implementations I've run across are
usually application specific as you need to decide what to do with
arrays, conflicting keys, etc.

I think concatenation is the right operator for shallow merge, it's
basically what would happen if you concatenated text representations of
two json objects and replaced the closing and opening braces between
them with a comma:

(substring(a::text, 1, length(a::text)-1) || ',' || substring(b::text,
2))::jsonb

Deep merge could be a function with flags to say what to do about
conflicts, etc.

/ Oskari

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

On 05/18/2015 12:11 AM, Peter Geoghegan wrote:

On Sun, May 17, 2015 at 8:41 PM, Josh Berkus <josh@agliodbs.com> wrote:

Is there a particular reason why "+" makes more sense as "shallow
concatination" and "||" makes more sense as "deep concatination"? Like,
something in JS or other client languages which would make that
preference make more sense to users?

While I hate last-minute changes in general, once we have this
functionality as || we won't be able to swap operators later if we
decide that deep concatination should have been ||. So I want to be
clear on why users will prefer that to + .

This guy is talking about the concatenation operator in hstore:
https://twitter.com/toolmantim/status/589348855302344705

I don't want to present this operator as being the equivalent for
jsonb (the thing you use for assignment). I wish it was, but it just
isn't, as long as it treats the first nesting level as special. jsonb
is all about nesting in general, and its concatenate operator must
reflect this. It wouldn't be much use if "operator @> jsonb" didn't
care about nesting either, but it does (unlike hstore's equivalent,
because hstore doesn't nest).

I don't think that (say) an "operator + jsonb" ought to be called a
concatenation operator at all. The idea is to distance what we have
here from the idea of an hstore concatenate operator, and to encourage
the understanding that it has only a specialized use. I think that the
danger of someone making the wrong assumption about the new "operator
|| jsonb" is very real (I think that the reverse wouldn't be true,
though, if concatenation worked in a nested fashion -- that wouldn't
bother users that had non-nested jsonb documents).

As I said, I don't think that my preference for deep concatenation is
a matter of taste. I think that shallow concatenation is fundamentally
and objectively at odds with what jsonb is supposed to be (as long as
concatenation is the way "nested assignment" works, which is what
users have been taught to think).

I'm not going to react to a Twitter conversation. I honestly think you
are massively overstating your case here. And forget about hstore. It's
really not relevant here. This code had its origin in NESTED hstore,
which we don't have, but it's flown the nest.

Once again, nobody is arguing against deep of recursive concatenation,
so you're really arguing against a straw man. It just happens to be
what we don't have.

As between || and + I'm personally moderately indifferent. I think
you're representing some body of understanding about the effects of
certain operators as being widespread when that's very far from clear.

You really still haven't said what you want us to do. Mostly you have
expressed your unhappiness without suggesting a path forward. That's not
helpful.

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

On Sun, May 17, 2015 at 9:41 PM, Josh Berkus <josh@agliodbs.com> wrote:

Is there a particular reason why "+" makes more sense as "shallow
concatination" and "||" makes more sense as "deep concatination"? Like,
something in JS or other client languages which would make that
preference make more sense to users?

As someone who uses JSON day-to-day in Javascript and Python, I personally
don't think || or + matters much. Python uses json.loads() for JSON concat
and you have use a 3rd-party library in Javascript if you want that
functionality such as JQuery.extends(). I agree with Peter that we need
deep concatenation, but I don't think there is any standard for the
operator. I think the word "shallow" should be added to the docs though.

What is far more important than shallow or deep concatenation for the
document database use case is being able to delete or replace/update a
specific, nested path in the JSON object. It looks like that is possible
with the minus operator and jsonb_replace(). This is great, however it took
me awhile to figure out the path syntax. I think adding a paragraph to the
docs explaining the path syntax would help.

Ryan Pedela

On Mon, May 18, 2015 at 8:41 AM, Ryan Pedela <rpedela@datalanche.com> wrote:

On Sun, May 17, 2015 at 9:41 PM, Josh Berkus <josh@agliodbs.com> wrote:

Is there a particular reason why "+" makes more sense as "shallow

concatination" and "||" makes more sense as "deep concatination"? Like,
something in JS or other client languages which would make that
preference make more sense to users?

As someone who uses JSON day-to-day in Javascript and Python, I personally
don't think || or + matters much. Python uses json.loads() for JSON concat
and you have use a 3rd-party library in Javascript if you want that
functionality such as JQuery.extends(). I agree with Peter that we need
deep concatenation, but I don't think there is any standard for the
operator. I think the word "shallow" should be added to the docs though.

What is far more important than shallow or deep concatenation for the
document database use case is being able to delete or replace/update a
specific, nested path in the JSON object. It looks like that is possible
with the minus operator and jsonb_replace(). This is great, however it took
me awhile to figure out the path syntax. I think adding a paragraph to the
docs explaining the path syntax would help.

Having looked at this more, I think I understand the problem Peter has
identified and it is a significant usability problem in my opinion. I think
the word "concatenate" has confused me because I think of it as a
higher-level operation when I want to merge two, large JSON objects which
isn't a very common operation, at least for me. What is absolutely required
for the document database use case is the following:

1. Get element at any arbitrary path. ( #> operator )
2. Delete any arbitrary path. ( minus operator )
3. Replace/update element at any arbitrary path. ( jsonb_replace )
4. Add element to any arbitrary path. ( ? )

It is #4 that does not seem to exist unless jsonb_replace() creates the
specified path if it does not exist. Does it? I am not currently at my desk
to test it myself.

If not, deep concatenation would solve this problem, but I can also see
another solution. Use + for shallow concatenation since it really means
"add element to top-level path" as Peter suggests. Then add another
function: jsonb_add( target jsonb, path text[], new jsonb ) to add element
at any arbitrary path. Then leave || for deep concatenation in 9.6 or
whenever.

If jsonb_replace() satisfies #4 then I think everything is fine. Without #4
however, jsonb would remain an incomplete document database solution in my
opinion.

Thanks,
Ryan Pedela

creates the specified path if it does not exist. Does it?

No, jsonb_replace() doesn't create an element, if it doesn't exist. I
think, otherwise it can be confusing, so probably jsonb_add() may be more
appropriate (and, actually, this function was already mentioned in previous
discussions).

On 18 May 2015 at 22:57, Ryan Pedela <rpedela@datalanche.com> wrote:

On Mon, May 18, 2015 at 5:05 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

As between || and + I'm personally moderately indifferent. I think you're
representing some body of understanding about the effects of certain
operators as being widespread when that's very far from clear.

You really still haven't said what you want us to do. Mostly you have
expressed your unhappiness without suggesting a path forward. That's not
helpful.

I think I was very clear on what I see as a path forward. The
documentation and general presentation of the operator (e.g. advocacy
stuff) needs to make clear that it isn't suitable for "nested
assignment" in the same way that the hstore concatenate operator is. I
think it has been understood in this way by people so far. This is my
major concern.

So I think we should use the + operator to distance this from the
hstore concatenate operator, which *is* widely understood to be mostly
useful for "nested assignment". And I think that the documentation
should be updated to make the actual uses of the operator very clear.
Granted, the documentation for this new operator does not say anything
about "nested assignment" for the new concatenate operator as things
stand, but it says very little.

When I saw people getting excited about this new feature, I assumed
that it addressed what some people saw as the major omission in jsonb.
Unfortunately, it doesn't, and it behooves us to make sure that that
is understood. My complaint is mostly about the *positioning* of the
operator.
--
Peter Geoghegan

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

On 05/18/2015 01:18 PM, Peter Geoghegan wrote:

So I think we should use the + operator to distance this from the
hstore concatenate operator, which *is* widely understood to be mostly
useful for "nested assignment".

Having trouble scanning this. Since hstore isn't nested what the heck
does "nested assignment" mean w.r.t. hstore?

In any case, it seems clear from other posters that your perceptions and
expectations are not universally held.

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