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"]