Links

JSON_PATH

Description

The JSON_PATH function allows you to query specific fields within a JSON object using a JSON path expression. This is particularly useful for extracting nested values or values from arrays within a JSON string.

Syntax

JSON_PATH(path, JSON)

Arguments

Name
Type
Description
Default Value
path
string
The JSON path expression to navigate within the JSON object.
JSON
string
The JSON object from which to extract the value.

Input Record Example

Here is an example of an input record containing nested JSON and an array:
{
"order": {
"id": "123",
"items": ["Laptop", "Mouse", "Keyboard"]
},
"customer": {
"name": "John Doe"
}
}

Examples

Basic Example

JSON_PATH('$.order.id', '{"order": {"id": "123", "items": ["Laptop", "Mouse", "Keyboard"]}, "customer": {"name": "John Doe"}}')
Output: 123

Array Example

JSON_PATH('$.order.items[*]', '{"order": {"id": "123", "items": ["Laptop", "Mouse", "Keyboard"]}, "customer": {"name": "John Doe"}}')
Output: ["Laptop", "Mouse", "Keyboard"]

Transformation Job Example

CREATE JOB json_path_array_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT value,
JSON_PATH('$.order.id', value) AS OrderID,
JSON_PATH('$.order.items[*]', value) AS OrderItems
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query Result

value
OrderID
OrderItems
{ "order": { "id": "123", "items": ["Laptop", "Mouse", "Keyboard"] }, "customer": { "name": "John Doe" } }
123
["Laptop", "Mouse", "Keyboard"]