Extract a JSON sub-value from a JSON-formatted string using a JSONPath expression.
GET_JSON_OBJECT(json_str, path)## Overview Extracts a scalar value, object, or array from a JSON-formatted string using a JSONPath expression and returns the match as a STRING. Objects and arrays are returned as their minified JSON text; primitive values (strings, numbers, booleans) are returned as the value's string form. Use this function when your table stores JSON in a STRING column and you need ad-hoc access to nested fields without first reshaping the data. GET_JSON_OBJECT is designed to be forgiving: NULL input, malformed JSON, and paths that fail to resolve all yield NULL rather than raising an error. This makes it well suited to large event and log tables where a small fraction of rows may be malformed and you do not want a single bad row to fail the entire query. ## Behavior - Returns NULL if the input string is NULL. - Returns NULL if the input cannot be parsed as JSON (parse errors are swallowed, not raised). - Returns NULL if the path expression does not resolve to a value. - Returns a JSON null as the STRING "null". Use a CAST or CASE to distinguish from a missing key. - Return type is always STRING. Cast explicitly to DOUBLE, BIGINT, BOOLEAN, and so on when you need a typed value. - Object and array matches are serialized as minified JSON text (no whitespace), preserving key order. - Parsing cost is paid on every call. For repeated extractions from the same document, parsing once with FROM_JSON into a STRUCT and reusing the result is significantly faster. - Safe for use in filters and projections; not safe to rely on for strict schema validation (use FROM_JSON with a strict schema for that). ## JSON path syntax - `$` root of the document (required as the first character). - `.name` field accessor for an object key that is a valid identifier. - `['name']` bracketed field accessor, required for keys that contain special characters or spaces. - `[N]` zero-based array index (for example `[0]` for the first element). - Chained accessors navigate deeper (for example `$.a.b[2].c`). - Wildcards and filter predicates beyond simple field and index access are not supported; use FROM_JSON when you need a full JSONPath engine. ## Compatibility - Path grammar is a restricted subset of JSONPath as described in the JSONPath RFC draft. - Parse rules follow RFC 8259 (JSON data interchange format). - Behavior aligns with the `get_json_object` built-in used by other SQL-on-Hadoop engines and analytics SQL dialects.
| Name | Type | Description |
|---|---|---|
json_str | Specifies the JSON-formatted input string to extract from. Must be valid JSON per RFC 8259. If the input is NULL or cannot be parsed as JSON, the function returns NULL rather than raising an error. | |
path | Specifies a JSONPath expression identifying the value to extract. The path must begin with the root indicator '$' followed by a sequence of field accessors and array indexes (for example '$.user.name' or '$.items[0].price'). |
-- Extract a top-level scalar
SELECT GET_JSON_OBJECT('{"name": "Alice", "age": 30}', '$.name') AS name;-- Extract a nested field from a realistic payload
SELECT order_id,
GET_JSON_OBJECT(payload, '$.customer.email') AS email,
GET_JSON_OBJECT(payload, '$.shipping.city') AS city
FROM ecommerce.orders.raw_events;-- Extract by array index
SELECT GET_JSON_OBJECT('{"items": [10, 20, 30]}', '$.items[1]') AS second_item;-- Missing path returns NULL (not an error)
SELECT GET_JSON_OBJECT('{"a": 1}', '$.b') AS missing;-- NULL input and unparseable input both return NULL
SELECT GET_JSON_OBJECT(NULL, '$.any') AS null_input,
GET_JSON_OBJECT('not json', '$.any') AS bad_input;-- Combine with CAST for typed access in an aggregate
SELECT AVG(CAST(GET_JSON_OBJECT(payload, '$.response_time_ms') AS DOUBLE)) AS avg_rt_ms
FROM analytics.telemetry.page_events
WHERE GET_JSON_OBJECT(payload, '$.route') = '/checkout';