JSON_STRIP_NULLS

Recursively remove object keys whose value is JSON null.

Category: jsonReturns: JSONDialect: PostgreSql

Syntax

JSON_STRIP_NULLS(json)

Description

## Overview Returns a copy of the input document with every object key whose value is JSON null recursively removed. Use this function to compact sparse JSON before export, comparison, or storage, or to normalize documents where absent attributes are represented as explicit JSON null instead of omitted keys. Null values inside JSON arrays are not removed (removing an array element would shift positions and change semantics). For the binary-JSON variant, use JSONB_STRIP_NULLS. ## Behavior - Walks the document recursively, processing nested objects at any depth. - Removes only keys whose value is literally JSON null; keeps keys with empty strings, zero, false, empty objects, and empty arrays. - Does not modify array contents; a JSON null element inside an array remains. - Returns SQL NULL when the input is SQL NULL. - Returns the input unchanged when no null-valued keys exist. - Result is JSON (text). Use JSONB_STRIP_NULLS for the binary variant. ## Compatibility - Behavior aligns with the json_strip_nulls convention in SQL implementations that support JSON manipulation. - Output conforms to RFC 8259.

Parameters

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

Examples

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

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

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

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

-- Compact a wide sparse event before export
SELECT event_id, JSON_STRIP_NULLS(payload) AS compact_payload
FROM analytics.telemetry.events
WHERE event_date = DATE '2026-04-19';

Pitfalls

Null values inside arrays are retained. If you need to drop array nulls, extract, filter, and rebuild the array manually.
Only JSON 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. Downstream code that expects '{}' must COALESCE.
Key order is preserved in the output JSON text; do not rely on order for equality checks after round-trips.
Every call re-walks the whole document. For bulk transforms, consider doing the strip once during ingest rather than per-query.