Avoid undesired flattening of jsonb arrays?

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

Responses

Browse pgsql-general by date

  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