JSONB_STRIP_NULLS

Recursively remove object keys whose value is JSONB null.

Category: jsonReturns: JSONBDialect: PostgreSql

Syntax

JSONB_STRIP_NULLS(jsonb)

Description

## Overview Returns a copy of the input JSONB document with every object key whose value is JSONB null recursively removed. Use this to normalize sparse documents before equality comparison, containment checks, or export. Null values inside JSONB arrays are not removed; deleting an array element would shift positions and change semantics. For the text-JSON variant, use JSON_STRIP_NULLS. ## Behavior - Walks the document recursively, processing nested objects at any depth. - Removes only keys whose value is JSONB null. - Does not modify array contents; JSONB null elements inside arrays remain. - Returns SQL NULL for SQL NULL input. - Returns the input unchanged when no null-valued keys exist. - Result type is JSONB. ## Compatibility - Matches the jsonb_strip_nulls convention in widely adopted SQL implementations. - Uses JSONB canonical form.

Parameters

Name	Type	Description
`jsonb`		Specifies the JSONB document to process. The function walks all nested objects and removes any key whose value is JSONB null. SQL NULL input returns SQL NULL.

Examples

-- Remove null-valued keys from a flat object
SELECT JSONB_STRIP_NULLS('{"a": 1, "b": null, "c": 3}'::JSONB) AS clean;

-- Nested objects are processed recursively
SELECT JSONB_STRIP_NULLS('{"outer": {"keep": true, "remove": null}}'::JSONB) AS clean;

-- Null values inside arrays are preserved
SELECT JSONB_STRIP_NULLS('{"arr": [1, null, 3]}'::JSONB) AS preserved;

-- SQL NULL input returns SQL NULL
SELECT JSONB_STRIP_NULLS(NULL) AS null_in;

-- Realistic: compact sparse payloads before comparison
SELECT event_id
FROM analytics.telemetry.events a
JOIN analytics.telemetry.events b USING (user_id)
WHERE JSONB_STRIP_NULLS(a.payload) = JSONB_STRIP_NULLS(b.payload);

Pitfalls

Null values inside arrays are retained. To drop array nulls, extract, filter, and rebuild the array.
Only JSONB null is removed; keys with empty string '', zero 0, false, empty {}, and empty [] are kept.
SQL NULL input returns SQL NULL, not an empty object. COALESCE if downstream code expects '{}'.
Canonicalization may reorder keys; relying on textual equality after this function is unsafe.
Stripping does not rewrite the underlying storage; per-call cost is proportional to document size.