JSON_BUILD_ARRAY

Build a JSON array from a variadic list of values.

Category: jsonReturns: JSONDialect: PostgreSql

Syntax

JSON_BUILD_ARRAY(val1, val2, ...)

Description

## Overview Constructs a JSON array from a variadic argument list. Each argument is converted to JSON using the same rules as TO_JSON, then placed in order in the resulting array. NULL arguments become JSON null entries, so the output array length always equals the argument count. Use JSON_BUILD_ARRAY when you need to assemble small, schema-free arrays inline (for example tags, coordinate pairs, or ordered tuples) without first building a SQL array and then converting it. For the binary variant with identical semantics, use JSONB_BUILD_ARRAY. ## Behavior - Returns an empty JSON array when called with no arguments. - NULL arguments are emitted as JSON null, not skipped. - Argument order is preserved in the output array. - The result type is JSON (text-based). Use JSONB_BUILD_ARRAY for the binary variant. - Composite types, arrays, and JSON values are serialized recursively. - No parse errors are possible; the function assembles JSON from typed inputs. ## Compatibility - Follows the SQL/JSON constructor pattern in SQL:2016. - Output conforms to RFC 8259.

Parameters

Name	Type	Description
`val`		Specifies one or more values to include as elements of the resulting JSON array. Each argument is converted to its JSON representation using the same rules as TO_JSON. NULL arguments are emitted as JSON null entries rather than skipped.

Examples

-- Build an array from mixed scalar types
SELECT JSON_BUILD_ARRAY(1, 'two', true, NULL) AS arr;

-- Empty arg list produces an empty array
SELECT JSON_BUILD_ARRAY() AS arr;

-- Nested arrays
SELECT JSON_BUILD_ARRAY('outer', JSON_BUILD_ARRAY(1, 2, 3)) AS nested;

-- Array of objects
SELECT JSON_BUILD_ARRAY(
  JSON_BUILD_OBJECT('id', 1, 'name', 'alpha'),
  JSON_BUILD_OBJECT('id', 2, 'name', 'beta')
) AS items;

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

Pitfalls

NULL arguments are emitted as JSON null, not skipped. If you need to omit NULLs, filter with JSON_STRIP_NULLS on an object or pre-filter the arguments.
The result is JSON (text). For containment, key-existence, or index lookups, cast to JSONB or use JSONB_BUILD_ARRAY.
Strings are JSON-encoded (quoted and escaped). A TEXT argument 'hello' becomes '"hello"' in the output, not unquoted 'hello'.
Large variadic arg lists can hit function-call size limits; prefer JSON_AGG to build arrays from row sets.