JSONB_EXTRACT_PATH_TEXT

Extract a JSONB sub-value at a path and return it as text.

Category: jsonReturns: STRINGDialect: PostgreSql

Syntax

JSONB_EXTRACT_PATH_TEXT(jsonb, key1, key2, ...)

Description

## Overview Extracts a JSONB sub-value at a path of keys and returns it as TEXT. Traversal rules are identical to JSONB_EXTRACT_PATH, but the result is coerced to a STRING rather than preserving JSONB typing. JSON string values are returned without their surrounding double quotes. Use the text variant when you need a scalar for string concatenation, CAST-then-compare patterns, or external systems that expect TEXT. For the typed variant, use JSONB_EXTRACT_PATH. ## Behavior - Returns NULL when the path does not resolve. - Returns NULL for NULL input. - A JSONB null at the target path is returned as SQL NULL (not distinguishable from a missing path; use JSONB_EXTRACT_PATH + JSONB_TYPEOF when the distinction matters). - JSONB strings are returned without surrounding double quotes. - Numbers and booleans are returned as their text form ('42', 'true'). - Objects and arrays are returned as minified JSON text. ## Compatibility - Matches the jsonb_extract_path_text convention in widely adopted SQL dialects. - Output conforms to RFC 8259 for object and array serializations.

Parameters

Name	Type	Description
`jsonb`		Specifies the JSONB document to extract from. 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 string scalar without surrounding quotes
SELECT JSONB_EXTRACT_PATH_TEXT('{"name": "Carol"}'::JSONB, 'name') AS name;

-- Numeric scalar returned as text
SELECT JSONB_EXTRACT_PATH_TEXT('{"score": 95}'::JSONB, 'score') AS score_text;

-- Deep path
SELECT JSONB_EXTRACT_PATH_TEXT('{"a": {"b": {"c": "found"}}}'::JSONB, 'a', 'b', 'c') AS val;

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

-- Cast extracted text for numeric filtering
SELECT event_id
FROM analytics.telemetry.events
WHERE CAST(JSONB_EXTRACT_PATH_TEXT(payload, 'status_code') AS INTEGER) >= 500;

Pitfalls

A JSONB null at the target returns SQL NULL, indistinguishable from a missing key.
Numeric results are returned as TEXT; comparisons with numeric literals must CAST to a numeric type to avoid lexicographic ordering.
Array indexes must be STRINGs ('0', '1', ...).
Matching is case-sensitive; no key normalization is performed.
Objects and arrays are serialized as minified JSON text, which may be larger than expected if the node is deep.