Redshift JSON_EXTRACT_PATH_TEXT in Snowflake, Databricks and BigQuery
Snowflake keeps the name JSON_EXTRACT_PATH_TEXT but takes a single dotted path instead of separate path elements. Databricks uses get_json_object and BigQuery JSON_VALUE, both with a $.a.b path.
Redshift function: JSON_EXTRACT_PATH_TEXT('json_string', 'path_elem' [, 'path_elem' ...] [, null_if_invalid]) · Redshift docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Different name | JSON_EXTRACT_PATH_TEXT(column, 'a.b') | One path string in dotted notation, for example 'customer.address.city'; returns NULL when the path does not exist. | Snowflake docs |
| Databricks | Rewrite | get_json_object(expr, '$.a.b') | Returns NULL when the path is missing. For new code Databricks recommends VARIANT columns and the : operator. | Databricks docs |
| BigQuery | Rewrite | JSON_VALUE(json_string, '$.a.b') | Returns a scalar as STRING; use JSON_QUERY for objects and arrays. | BigQuery docs |
Behavior differences to check
- JSON_EXTRACT_PATH_TEXT(js, 'customer', 'address', 'city') becomes a single path: 'customer.address.city' in Snowflake, '$.customer.address.city' in Databricks and BigQuery.
- Redshift fails on invalid JSON unless null_if_invalid is true. Check how the target treats invalid input before relying on errors to catch bad data.
Examples
City from a JSON payload
SELECT JSON_EXTRACT_PATH_TEXT(payload, 'customer', 'city') AS city FROM events;SELECT JSON_EXTRACT_PATH_TEXT(payload, 'customer.city') AS city FROM events;SELECT get_json_object(payload, '$.customer.city') AS city FROM events;SELECT JSON_VALUE(payload, '$.customer.city') AS city FROM events;