JSON_EXTRACT_PATH_TEXT

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

Category: jsonReturns: STRINGDialect: PostgreSql

Syntax

JSON_EXTRACT_PATH_TEXT(json, key1, key2, ...)

Description

## Overview Extracts a JSON sub-value at the path described by a sequence of keys and returns it as TEXT. This is the text-flavored companion to JSON_EXTRACT_PATH: the traversal rules are identical, but the result is coerced to a STRING rather than preserving JSON typing. JSON string values are returned without surrounding quotes, making the result immediately usable in string operations. Use JSON_EXTRACT_PATH_TEXT when you want a scalar result for comparisons, aggregations, or string concatenation and do not need the JSON typing metadata. For the binary variant, use JSONB_EXTRACT_PATH_TEXT. ## Behavior - Returns NULL if the path does not resolve. - Returns NULL if the JSON argument is NULL. - Raises an error on unparseable JSON. - A JSON null at the target path is returned as SQL NULL (distinct from JSON_EXTRACT_PATH, which returns JSON null). - JSON 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 json_extract_path_text convention used by widely adopted SQL dialects. - JSON parsing follows RFC 8259.

Parameters

Name	Type	Description
`json`		Specifies the JSON document to extract from. Returns NULL if the value is NULL. Raises an error if the value is not valid JSON.
`keys`		Specifies one or more text keys that form the path. Each key descends one level. Use a zero-based numeric string (for example '0') to index into an array.

Examples

-- Extract a string scalar as plain text (no surrounding quotes)
SELECT JSON_EXTRACT_PATH_TEXT('{"name": "Alice"}', 'name') AS name;

-- Numeric values come back as text
SELECT JSON_EXTRACT_PATH_TEXT('{"count": 42}', 'count') AS count_text;

-- Deep nesting
SELECT JSON_EXTRACT_PATH_TEXT('{"a": {"b": {"c": "deep"}}}', 'a', 'b', 'c') AS val;

-- Missing key returns NULL
SELECT JSON_EXTRACT_PATH_TEXT('{"x": 1}', 'missing_key') AS missing;

-- Boolean scalar returned as 'true' / 'false'
SELECT JSON_EXTRACT_PATH_TEXT('{"active": true}', 'active') AS active_text;

-- Realistic cast-and-filter pattern
SELECT request_id
FROM analytics.telemetry.events
WHERE CAST(JSON_EXTRACT_PATH_TEXT(payload, 'status_code') AS INTEGER) >= 500
  AND event_date = DATE '2026-04-19';

Pitfalls

A JSON null at the target returns SQL NULL, indistinguishable from a missing path. Use JSON_EXTRACT_PATH followed by JSON_TYPEOF when that distinction matters.
Numbers come back as TEXT. Comparisons with numeric literals will be string comparisons unless you CAST the result (for example '9' > '10' is TRUE as TEXT).
Array indexes must be passed as STRINGs ('0', '1', ...). Integer literals will not match array positions.
Invalid JSON raises an error. Pre-filter or wrap in a TRY pattern when a small fraction of rows may be malformed.
The surrounding double quotes of JSON strings are stripped, but internal escapes are not un-escaped beyond what RFC 8259 requires.