From: | "Joel Jacobson" <joel(at)compiler(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Avoid undesired flattening of jsonb arrays? |
Date: | 2020-12-18 15:23:40 |
Message-ID: | 0d72b76d-ca2b-4263-8888-d6dfca861c51@www.fastmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2020-12-18 15:33:51 | Re: Avoid undesired flattening of jsonb arrays? |
Previous Message | Ron | 2020-12-18 13:23:51 | Re: Unexpected result count from update statement on partitioned table |