JSONB_BUILD_ARRAY

Build a JSONB array from a variadic list of values.

Category: jsonReturns: JSONBDialect: PostgreSql

Syntax

JSONB_BUILD_ARRAY(val1, val2, ...)

Description

## Overview Constructs a JSONB array from a variadic argument list. Each argument is converted to JSONB using TO_JSONB rules, then placed in argument order. NULL arguments become JSONB null entries, so the output length always equals the argument count. Use JSONB_BUILD_ARRAY when the result will be indexed, compared, or repeatedly traversed; use JSON_BUILD_ARRAY when you only need a text JSON value. Because JSONB is canonicalized at construction, object elements inside the array have their duplicate keys resolved (last-wins) before insertion. ## Behavior - Returns an empty JSONB array when called with no arguments. - NULL arguments become JSONB null entries. - Argument order is preserved. - Composite types and JSONB values are serialized recursively. - Duplicate object keys in object-typed arguments are deduplicated by JSONB canonicalization. - Output type is JSONB; use JSON_BUILD_ARRAY for the text variant. ## Compatibility - Follows the SQL/JSON constructor pattern in SQL:2016. - JSONB canonical form differs from JSON in that key order and whitespace are not preserved.

Parameters

Name	Type	Description
`val`		Specifies one or more values to include as elements of the resulting JSONB array. Each argument is converted to JSONB using the same rules as TO_JSONB. NULL arguments become JSONB null entries.

Examples

-- Build a JSONB array from mixed scalar types
SELECT JSONB_BUILD_ARRAY(1, 'two', true, NULL) AS arr;

-- Empty argument list yields an empty array
SELECT JSONB_BUILD_ARRAY() AS arr;

-- Nested JSONB arrays
SELECT JSONB_BUILD_ARRAY('outer', JSONB_BUILD_ARRAY(10, 20)) AS nested;

-- Combine with JSONB_BUILD_OBJECT
SELECT JSONB_BUILD_ARRAY(
  JSONB_BUILD_OBJECT('id', 1, 'label', 'alpha'),
  JSONB_BUILD_OBJECT('id', 2, 'label', 'beta')
) AS items;

-- Build per-row JSONB arrays from columns for export
SELECT user_id, JSONB_BUILD_ARRAY(first_name, last_name, email) AS identity
FROM iam.directory.users;

Pitfalls

NULL arguments become JSONB null entries, not skipped. Filter upstream if you need them omitted.
Object elements inside the array are canonicalized: duplicate keys are silently deduplicated (last-wins).
Output is JSONB; when comparing to a JSON literal built with JSON_BUILD_ARRAY, normalize both sides by casting to the same type.
Large variadic arg lists can hit function-call limits; prefer JSONB_AGG to build arrays from row sets.
String arguments are JSON-encoded (quoted and escaped). A TEXT 'hello' becomes '"hello"' inside the array.