JSONB_BUILD_OBJECT

Build a JSONB object from alternating key-value arguments.

Category: jsonReturns: JSONBDialect: PostgreSql

Syntax

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

Description

## Overview Constructs a JSONB object from a variadic argument list interpreted as alternating key-value pairs. Odd-positioned arguments are keys (STRING); even-positioned arguments are values of any type, converted by TO_JSONB rules. Because JSONB is canonicalized at construction, duplicate keys are deduplicated (last-wins) rather than preserved. This is the main semantic difference from JSON_BUILD_OBJECT, which preserves duplicate keys in the text output. ## Behavior - Raises an error if the argument count is odd (unbalanced key-value pairs). - Raises an error if any key is NULL. - NULL values become JSONB null entries; the key is still emitted. - Duplicate keys are deduplicated by JSONB canonical form; the last value provided for a given key wins. - Result type is JSONB; use JSON_BUILD_OBJECT for the text variant. - Key order in the canonical form is not guaranteed to match argument order. ## Compatibility - Follows the SQL/JSON constructor pattern in SQL:2016. - JSONB canonical form is used for storage and comparison.

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 JSONB using TO_JSONB rules. NULL values produce JSONB null entries.

Examples

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

-- NULL value becomes JSONB null
SELECT JSONB_BUILD_OBJECT('key', NULL) AS obj;

-- Nested JSONB object
SELECT JSONB_BUILD_OBJECT('address', JSONB_BUILD_OBJECT('city', 'London', 'zip', 'EC1A')) AS obj;

-- Combine with JSONB_BUILD_ARRAY
SELECT JSONB_BUILD_OBJECT('ids', JSONB_BUILD_ARRAY(1, 2, 3)) AS obj;

-- Emit per-row JSONB from a realistic table
SELECT JSONB_BUILD_OBJECT(
  'order_id', order_id,
  'status', status,
  'total', total
) AS order_doc
FROM ecommerce.sales.orders;

Pitfalls

Duplicate keys are silently deduplicated (last-wins). If callers relied on JSON-level duplicate preservation, this is a behavior change.
NULL keys raise an error, but NULL values are accepted and become JSONB null. The asymmetry catches first-time users.
Odd argument count raises an error. Keep a mental model of alternating key-value pairs when editing long call sites.
Key order in JSONB canonical form is not guaranteed to match argument order; do not rely on textual round-trip equality.
Output is JSONB; comparing against JSON values requires casting to the same type.