JSONB_EXTRACT_PATH

Extract a JSONB sub-value by traversing a sequence of keys.

Category: jsonReturns: JSONBDialect: PostgreSql

Syntax

JSONB_EXTRACT_PATH(jsonb, key1, key2, ...)

Description

## Overview Extracts a JSONB sub-value by following a sequence of text keys. Each key descends one level into objects; numeric-looking keys are interpreted as array indexes when the current node is an array. The result preserves JSONB typing so follow-up calls such as JSONB_TYPEOF and JSONB_ARRAY_LENGTH work directly. Use this function when keys are known at query construction time (for example passed as parameters or column values). For the text-result variant use JSONB_EXTRACT_PATH_TEXT. ## Behavior - Returns NULL when any key in the path fails to resolve. - Returns NULL for NULL input. - Preserves JSONB typing: objects, arrays, and scalars are returned as JSONB. - A JSONB null at the target path is returned as JSONB null (distinct from SQL NULL). - Array indexes must be zero-based numeric STRINGs. - Traversal stops at the first missing key; remaining keys are not evaluated. ## Compatibility - Matches the jsonb_extract_path convention in widely adopted SQL dialects. - Uses JSONB canonical form.

Parameters

Name	Type	Description
`jsonb`		Specifies the JSONB document to traverse. Returns NULL for NULL input.
`keys`		Specifies one or more text keys that form the path. Each key descends one level. Use zero-based numeric STRINGs (for example '0') to index into arrays.

Examples

-- Extract a top-level field
SELECT JSONB_EXTRACT_PATH('{"a": 1, "b": 2}'::JSONB, 'a') AS val;

-- Nested field
SELECT JSONB_EXTRACT_PATH('{"user": {"name": "Bob"}}'::JSONB, 'user', 'name') AS name;

-- Array element by index
SELECT JSONB_EXTRACT_PATH('{"items": [100, 200, 300]}'::JSONB, 'items', '2') AS third;

-- Missing path returns NULL
SELECT JSONB_EXTRACT_PATH('{"x": 1}'::JSONB, 'y') AS absent;

-- Realistic: pull nested fields from an events table
SELECT event_id,
       JSONB_EXTRACT_PATH(payload, 'user', 'id') AS user_id
FROM analytics.telemetry.events
WHERE event_date = DATE '2026-04-19';

Pitfalls

Array indexes must be STRINGs ('0', '1', ...), not INTEGERs. Passing an INTEGER is a common source of unexpected NULL results.
A JSONB null at the target is returned as JSONB null, not SQL NULL. Use JSONB_TYPEOF to tell missing from explicit null.
Key matching is case-sensitive.
Casting the result to a scalar type is required for numeric or date comparisons.
Traversal errors are silent (return NULL); to distinguish malformed documents from missing keys, validate shape with JSONB_TYPEOF first.