Convert a SQL value to its JSONB representation.
TO_JSONB(value)## Overview Converts an arbitrary SQL value to its JSONB representation. Conversion rules match TO_JSON, but the result is stored in JSONB binary form, which supports indexing, containment (@>), key existence (?), and path operators. Use TO_JSONB when the resulting value will be compared, queried, or stored in a JSONB column. JSONB canonicalizes key order and deduplicates object keys, so round-trip equality with the source text is not guaranteed. ## Behavior - Strings are JSON-encoded (quoted and escaped per RFC 8259). - Numbers and booleans are stored in JSONB canonical form. - SQL NULL returns SQL NULL; to emit JSONB null, use 'null'::JSONB. - Composite types and arrays are converted recursively. - Duplicate keys in composite types are deduplicated (last wins). - Key ordering in canonical form is not guaranteed to match source column order. - Result type is JSONB. ## Compatibility - Follows the SQL/JSON TO_JSONB semantics from SQL:2016. - Output parses as JSON per RFC 8259.
| Name | Type | Description |
|---|---|---|
value | Specifies the value to convert to JSONB. Accepts strings, numbers, booleans, arrays, composite types, and NULL. Conversion rules are identical to TO_JSON but the output is stored in JSONB binary form with deduplicated keys. |
-- Convert a string to JSONB (JSON-quoted)
SELECT TO_JSONB('hello'::TEXT) AS val;-- Convert an integer
SELECT TO_JSONB(42) AS val;-- Convert a boolean
SELECT TO_JSONB(true) AS val;-- Convert a row to JSONB with named fields
SELECT TO_JSONB(t) AS doc
FROM (SELECT 1 AS id, 'Alice' AS name) t;-- Realistic: write out rows for indexed access
SELECT user_id, TO_JSONB(t) AS user_doc
FROM (SELECT user_id, display_name, email FROM iam.directory.users) t;-- Compare JSONB documents with containment
SELECT COUNT(*)
FROM analytics.telemetry.events
WHERE payload @> TO_JSONB('{"status": "ok"}'::JSON);