JSON_EXTRACT_PATH

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

Category: jsonReturns: JSONDialect: PostgreSql

Syntax

JSON_EXTRACT_PATH(json, key1, key2, ...)

Description

## Overview Extracts a JSON sub-value from a document by following a sequence of text keys. Each key traverses one level deeper into objects; numeric-looking keys are interpreted as array indexes when the current node is an array. The result is returned as JSON, preserving the original typing of the matched value. Use this function when your keys are known column values or parameters rather than a JSONPath string. For the binary-JSON variant, use JSONB_EXTRACT_PATH. To flatten the result to plain TEXT, use JSON_EXTRACT_PATH_TEXT. ## Behavior - Returns NULL if any key in the path does not resolve. - Returns NULL if the JSON argument is NULL. - Raises an error if the JSON argument is not valid JSON. - The result preserves JSON typing: objects and arrays are returned as JSON, primitives as JSON scalars. - JSON null values at the target path are returned as JSON null, distinct from SQL NULL (which would indicate a missing path). - Array access uses zero-based numeric indexes encoded as STRING (for example '0', '1'). ## Compatibility - Follows the json_extract_path pattern from widely used SQL dialects. - Parsing conforms to RFC 8259.

Parameters

Name	Type	Description
`json`		Specifies the JSON document to traverse. Must be valid JSON. Returns NULL if this argument is NULL.
`keys`		Specifies one or more text keys that form the path. Each key descends one level into the document. For array elements, use the zero-based numeric index as a STRING (for example '0' for the first element).

Examples

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

-- Extract a nested field
SELECT JSON_EXTRACT_PATH('{"user": {"name": "Alice", "age": 30}}', 'user', 'name') AS name;

-- Navigate into an array element
SELECT JSON_EXTRACT_PATH('{"items": [10, 20, 30]}', 'items', '1') AS second;

-- Missing key returns NULL, not an error
SELECT JSON_EXTRACT_PATH('{"a": 1}', 'b') AS absent;

-- Extract an entire nested object
SELECT JSON_EXTRACT_PATH('{"config": {"debug": true, "level": 5}}', 'config') AS obj;

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

Pitfalls

Array indexes must be passed as STRINGs ('0', '1', ...), not integers. Passing an INTEGER literal is a common cause of unexpected NULL results.
A JSON null at the target path is returned as JSON null (the value), not SQL NULL. Use JSON_TYPEOF to distinguish this from a missing path.
Invalid JSON in the first argument raises a parse error and fails the whole query. Pre-validate or wrap in a TRY pattern when data quality is mixed.
The return type is JSON. Casting to the expected scalar type (BIGINT, DOUBLE, BOOLEAN) is required when comparing or aggregating.
Keys are text-based and case-sensitive. Matching is exact; no normalization is performed.