|| (concatenation)

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

Purpose: Concatenate two jsonb values. The rule for deriving the output value depends upon the JSON data types of the operands.

Signature:

input values:       jsonb || jsonb
return value:       jsonb

Notes: There is no json overload.

If both sides of the operator are primitive JSON values, then the result is an array of these values:

do $body$
declare
  j_left constant jsonb := '17';
  j_right constant jsonb := '"x"';
  j_expected constant jsonb := '[17, "x"]';
begin
  assert
    j_left || j_right = j_expected,
 'unexpected';
end;
$body$;

If one side is a primitive JSON value and the other is an array , then the result is an array:

do $body$
declare
  j_left constant jsonb := '17';
  j_right constant jsonb := '["x", true]';
  j_expected constant jsonb := '[17, "x", true]';
begin
  assert
    j_left || j_right = j_expected,
 'unexpected';
end;
$body$;

If each side is an object, and no key-value pair in the RHS object has the same key as any key-value pair in the LHS object then the result is an object with all of the key-value pairs present:

do $body$
declare
  j_left constant jsonb := '{"a": 1, "b": 2}';
  j_right constant jsonb := '{"p":17, "q": 19}';
  j_expected constant jsonb := '{"a": 1, "b": 2, "p": 17, "q": 19}';
begin
  assert
    j_left || j_right = j_expected,
 'unexpected';
end;
$body$;

If the key of any key-value pair in the RHS object collides with a key of a key-value pair in the LHS object, then the key-value pair from the RHS object wins, just as when the keys of such pairs collide in a single object:

do $body$
declare
  j_left constant jsonb := '{"a": 1, "b": 2}';
  j_right constant jsonb := '{"p":17, "a": 19}';
  j_expected constant jsonb := '{"a": 19, "b": 2, "p": 17}';
begin
  assert
    j_left || j_right = j_expected,
 'unexpected';
end;
$body$;

If one side is an object and the other is an array, then the object is absorbed as a value in the array:

do $body$
declare
  j_left constant jsonb := '{"a": 1, "b": 2}';
  j_right constant jsonb := '[false, 42, null]';
  j_expected constant jsonb := '[{"a": 1, "b": 2}, false, 42, null]';
begin
  assert
    j_left || j_right = j_expected,
 'unexpected';
end;
$body$;