JSON_BUILD_OBJECT

Build a JSON object from alternating key-value arguments.

Category: jsonReturns: JSONDialect: PostgreSql

Syntax

JSON_BUILD_OBJECT(key1, value1, key2, value2, ...)

Description

## Overview Constructs a JSON object from a variadic argument list interpreted as alternating key-value pairs. Odd-positioned arguments are keys (STRING) and even-positioned arguments are values (any type, converted by TO_JSON rules). The function preserves insertion order in the output text. Use JSON_BUILD_OBJECT to assemble small, ad-hoc JSON documents inline without first building a composite type. For the binary variant with key deduplication, use JSONB_BUILD_OBJECT. ## Behavior - Raises an error if the argument count is odd (unbalanced key/value pairs). - Raises an error if any key argument is NULL. - NULL value arguments become JSON null entries (the key is still emitted). - Duplicate keys are preserved in the JSON (text) output. Downstream parsers typically pick the last, but both entries appear in the serialized form. - The result type is JSON. Use JSONB_BUILD_OBJECT when you need binary JSON with deduplicated keys. - Key order in the output matches argument order. ## Compatibility - Follows the SQL/JSON constructor pattern in SQL:2016. - Output conforms to RFC 8259.

Parameters

Name	Type	Description
`key`		Specifies the key for a key-value pair at an odd argument position. Must be a non-NULL STRING; NULL keys raise an error.
`value`		Specifies the value for the preceding key at an even argument position. Values are converted to their JSON representation using the same rules as TO_JSON. NULL values produce JSON null entries.

Examples

-- Build a simple object
SELECT JSON_BUILD_OBJECT('name', 'Alice', 'age', 30) AS obj;

-- NULL value becomes JSON null
SELECT JSON_BUILD_OBJECT('key', NULL) AS obj;

-- Nested object
SELECT JSON_BUILD_OBJECT('user', JSON_BUILD_OBJECT('id', 1, 'role', 'admin')) AS obj;

-- Combine with JSON_BUILD_ARRAY for realistic payloads
SELECT JSON_BUILD_OBJECT(
  'order_id', order_id,
  'items', JSON_BUILD_ARRAY(JSON_BUILD_OBJECT('sku', sku, 'qty', qty))
) AS payload
FROM ecommerce.sales.order_lines;

-- Emit per-row JSON shape for export
SELECT JSON_BUILD_OBJECT(
  'id', user_id,
  'name', display_name,
  'active', is_active
) AS user_json
FROM iam.directory.users;

Pitfalls

An odd number of arguments raises an error; the argument list must contain an even count of key-value pairs.
NULL keys raise an error, but NULL values are accepted and become JSON null. This asymmetry is easy to miss.
Duplicate keys are preserved in JSON text output. Consumers parse at their own discretion; for deduplicated behavior use JSONB_BUILD_OBJECT.
Keys are JSON-encoded, so a key string containing a double quote or backslash must be escaped in the source expression to appear correctly in the output.
Output is JSON text. For containment checks and key-existence operators, cast to JSONB or use the JSONB variant.