You're viewing version 3.4 of the OpenSearch documentation. This version is no longer maintained. For the latest version, see the current documentation. For information about OpenSearch version maintenance, see Release Schedule and Maintenance Policy.

JSON functions

PPL supports the following JSON functions for creating, parsing, and manipulating JSON data.

JSON path

All JSON paths used in JSON functions follow the format <key1>{<index1>}.<key2>{<index2>}.... Each <key> represents a field name. The {<index>} part is optional and is used only when the corresponding key refers to an array. For example:

a{2}.b{0}

This path accesses the element at index 0 in the b array, which is located within the element at index 2 of the a array.

Notes:

1. The {<index>} notation applies only when the associated key points to an array.
2. {} (without a specific index) is interpreted as a wildcard, equivalent to {*}, meaning all elements in the array at that level.

JSON

Usage: JSON(value)

Validates and parses a JSON string. Returns the parsed JSON value if the string is valid JSON, or NULL if invalid.

Parameters:

• value (Required): The string to validate and parse as JSON.

Return type: STRING

Example

source=json_test
| where json_valid(json_string)
| eval json=json(json_string)
| fields test_name, json_string, json

The query returns the following results:

JSON_VALID

Usage: JSON_VALID(value)

Evaluates whether a string uses valid JSON syntax. Returns TRUE if valid, FALSE if invalid. NULL input returns NULL.

Version: 3.1.0 Limitation: Only works when plugins.calcite.enabled=true

Parameters:

• value (Required): The string to validate as JSON.

Return type: BOOLEAN

Example

source=people
| eval is_valid_json = json_valid('[1,2,3,4]'), is_invalid_json = json_valid('{invalid}')
| fields is_valid_json, is_invalid_json
| head 1

The query returns the following results:

JSON_OBJECT

Usage: JSON_OBJECT(key1, value1, key2, value2, ...)

Creates a JSON object string from the specified key-value pairs. All keys must be strings.

Parameters:

• key1, value1 (Required): The first key-value pair. The key must be a string.
• key2, value2, ... (Optional): Additional key-value pairs.

Return type: STRING

Example

source=json_test
| eval test_json = json_object('key', 123.45)
| head 1
| fields test_json

The query returns the following results:

JSON_ARRAY

Usage: JSON_ARRAY(element1, element2, ...)

Creates a JSON array string from the specified elements.

Parameters:

• element1, element2, ... (Optional): The elements to include in the array. Can be any data type.

Return type: STRING

Example

source=json_test
| eval test_json_array = json_array('key', 123.45)
| head 1
| fields test_json_array

The query returns the following results:

JSON_ARRAY_LENGTH

Usage: JSON_ARRAY_LENGTH(value)

Returns the number of elements in a JSON array. Returns NULL if the input is not a valid JSON array, is NULL, or contains invalid JSON.

Parameters:

• value (Required): A string containing a JSON array.

Return type: INTEGER

Examples

The following example returns the length of a valid JSON array:

source=json_test
| eval array_length = json_array_length("[1,2,3]")
| head 1
| fields array_length

The query returns the following results:

The following example returns NULL for non-array JSON values:

source=json_test
| eval array_length = json_array_length("{\"1\": 2}")
| head 1
| fields array_length

The query returns the following results:

JSON_EXTRACT

Usage: JSON_EXTRACT(json_string, path1, path2, ...)

Extracts values from a JSON string using the specified JSON paths.

Behavior:

• Single path: Returns the extracted value directly.
• Multiple paths: Returns a JSON array containing the extracted values in path order.
• Invalid path: Returns NULL for that path in the result.

For path syntax details, see the JSON path section.

Parameters:

• json_string (Required): The JSON string to extract values from.
• path1, path2, ... (Required): One or more JSON paths specifying which values to extract.

Return type: STRING

Examples

The following example extracts values using a single JSON path:

source=json_test
| eval extract = json_extract('{"a": [{"b": 1}, {"b": 2}]}', 'a{}.b')
| head 1
| fields extract

The query returns the following results:

The following example extracts values using multiple JSON paths:

source=json_test
| eval extract = json_extract('{"a": [{"b": 1}, {"b": 2}]}', 'a{}.b', 'a{}')
| head 1
| fields extract

The query returns the following results:

JSON_DELETE

Usage: JSON_DELETE(json_string, path1, path2, ...)

Deletes values from a JSON string at the specified JSON paths. Returns the modified JSON string. If a path cannot find a value, no changes are made for that path.

Parameters:

• json_string (Required): The JSON string to delete values from.
• path1, path2, ... (Required): One or more JSON paths specifying which values to delete.

Return type: STRING

Examples

The following example deletes a value using a single JSON path:

source=json_test
| eval delete = json_delete('{"a": [{"b": 1}, {"b": 2}]}', 'a{0}.b')
| head 1
| fields delete

The query returns the following results:

The following example deletes values using multiple JSON paths:

source=json_test
| eval delete = json_delete('{"a": [{"b": 1}, {"b": 2}]}', 'a{0}.b', 'a{1}.b')
| head 1
| fields delete

The query returns the following results:

The following example shows no changes occur when trying to delete a non-existent path:

source=json_test
| eval delete = json_delete('{"a": [{"b": 1}, {"b": 2}]}', 'a{2}.b')
| head 1
| fields delete

The query returns the following results:

JSON_SET

Usage: JSON_SET(json_string, path1, value1, path2, value2, ...)

Sets values in a JSON string at the specified JSON paths. Returns the modified JSON string. If a path’s parent node is not a JSON object, that path is skipped.

Parameters:

• json_string (Required): The JSON string to modify.
• path1, value1 (Required): The first path-value pair to set.
• path2, value2, ... (Optional): Additional path-value pairs.

Return type: STRING

Examples

The following example sets a single value at a JSON path:

source=json_test
| eval jsonSet = json_set('{"a": [{"b": 1}]}', 'a{0}.b', 3)
| head 1
| fields jsonSet

The query returns the following results:

The following example sets multiple values using multiple path-value pairs:

source=json_test
| eval jsonSet = json_set('{"a": [{"b": 1}, {"b": 2}]}', 'a{0}.b', 3, 'a{1}.b', 4)
| head 1
| fields jsonSet

The query returns the following results:

JSON_APPEND

Usage: JSON_APPEND(json_string, path1, value1, path2, value2, ...)

Appends values to arrays in a JSON string at the specified JSON paths. Returns the modified JSON string. If a path’s target node is not an array, that path is skipped.

Parameters:

• json_string (Required): The JSON string to modify.
• path1, value1 (Required): The first path-value pair to append.
• path2, value2, ... (Optional): Additional path-value pairs.

Return type: STRING

Examples

The following example appends a value to an array:

source=json_test
| eval jsonAppend = json_append('{"a": [{"b": 1}]}', 'a', 3)
| head 1
| fields jsonAppend

The query returns the following results:

The following example shows paths to non-array targets are skipped:

source=json_test
| eval jsonAppend = json_append('{"a": [{"b": 1}, {"b": 2}]}', 'a{0}.b', 3, 'a{1}.b', 4)
| head 1
| fields jsonAppend

The query returns the following results:

The following example appends values using mixed path types:

source=json_test
| eval jsonAppend = json_append('{"a": [{"b": 1}]}', 'a', '[1,2]', 'a{1}.b', 4)
| head 1
| fields jsonAppend

The query returns the following results:

JSON_EXTEND

Usage: JSON_EXTEND(json_string, path1, value1, path2, value2, ...)

Extends arrays in a JSON string at the specified JSON paths with new values. Returns the modified JSON string. If a path’s target node is not an array, that path is skipped.

The function attempts to parse each value as an array:

• If parsing succeeds: The parsed array elements are added to the target array.
• If parsing fails: The value is treated as a single element and added to the target array.

Parameters:

• json_string (Required): The JSON string to modify.
• path1, value1 (Required): The first path-value pair to extend.
• path2, value2, ... (Optional): Additional path-value pairs.

Return type: STRING

Examples

The following example extends an array with a single value:

source=json_test
| eval jsonExtend = json_extend('{"a": [{"b": 1}]}', 'a', 3)
| head 1
| fields jsonExtend

The query returns the following results:

The following example shows paths to non-array targets are skipped:

source=json_test
| eval jsonExtend = json_extend('{"a": [{"b": 1}, {"b": 2}]}', 'a{0}.b', 3, 'a{1}.b', 4)
| head 1
| fields jsonExtend

The query returns the following results:

The following example extends an array by parsing the value as an array:

source=json_test
| eval jsonExtend = json_extend('{"a": [{"b": 1}]}', 'a', '[1,2]')
| head 1
| fields jsonExtend

The query returns the following results:

JSON_KEYS

Usage: JSON_KEYS(json_string)

Returns the keys of a JSON object as a JSON array. Returns NULL if the input is not a valid JSON object.

Parameters:

• json_string (Required): A string containing a JSON object.

Return type: STRING

Examples

The following example gets keys from a simple JSON object:

source=json_test
| eval jsonKeys = json_keys('{"a": 1, "b": 2}')
| head 1
| fields jsonKeys

The query returns the following results:

The following example gets keys from a nested JSON object:

source=json_test
| eval jsonKeys = json_keys('{"a": {"c": 1}, "b": 2}')
| head 1
| fields jsonKeys

The query returns the following results: