Postgres Pro
Downloads
Home   >   mailing lists

Thread: Avoid undesired flattening of jsonb arrays?

Avoid undesired flattening of jsonb arrays?

From
"Joel Jacobson"
Date:
The || operator for the jsonb type has a surprising behaviour.

Instead of appending the right operand "as is" to the left operand,
it has a magic behaviour if the right operand is an array,
in which case it will append the items of the array,
instead of appending the array itself as a single value.

Example:

SELECT '[10,20]'::jsonb || '30'::jsonb;
[10, 20, 30]

SELECT '[10,20]'::jsonb || '[30]'::jsonb;
[10, 20, 30]

Since [10, 20, [30]] is desired in our case, we must use jsonb_insert() to work-around the problem in a not very nice way:

SELECT jsonb_insert('[10,20]'::jsonb,'{-1}','[30]'::jsonb,TRUE);
[10, 20, [30]]

Suggestions welcome if there is a better way to solve this problem.

Best regards,

Joel

Re: Avoid undesired flattening of jsonb arrays?

From
"David G. Johnston"
Date:
On Fri, Dec 18, 2020 at 8:24 AM Joel Jacobson <joel@compiler.org> wrote:
The || operator for the jsonb type has a surprising behaviour.

Instead of appending the right operand "as is" to the left operand,
it has a magic behaviour if the right operand is an array,
in which case it will append the items of the array,
instead of appending the array itself as a single value.


It's not magic, and it is documented clearly.

I'll agree that the description could discuss the case explicitly, and the array||scalar case could be added to the examples.


Suggestions welcome if there is a better way to solve this problem.


As you are writing literals just put an array in the to-be-merged array.

select '["a","b"]'::jsonb || '[["c","d"]]'::jsonb

David J.

Re: Avoid undesired flattening of jsonb arrays?

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I'll agree that the description could discuss the case explicitly, and the
> array||scalar case could be added to the examples.

Yeah, the documentation completely fails to explain what happens
when the inputs aren't two arrays or two objects.  I'd kind of assumed
that that's an error, but it isn't.  Some experimentation indicates
that the behavior in all cases except two objects is to convert any
non-array input to a one-element array, reducing the situation to the
two-array case.

            regards, tom lane

Re: Avoid undesired flattening of jsonb arrays?

From
"Joel Jacobson"
Date:
The following nicer work-around was suggested to me by Andreas Karlsson:

-    jsonb_insert(x.jsonb_array,'{-1}',next_item.item,TRUE)
+    x.jsonb_array || jsonb_build_array(next_item.item)


On Fri, Dec 18, 2020, at 17:20, Tom Lane wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I'll agree that the description could discuss the case explicitly, and the
> array||scalar case could be added to the examples.

Yeah, the documentation completely fails to explain what happens
when the inputs aren't two arrays or two objects.  I'd kind of assumed
that that's an error, but it isn't.  Some experimentation indicates
that the behavior in all cases except two objects is to convert any
non-array input to a one-element array, reducing the situation to the
two-array case.

regards, tom lane