JSON Functions
Parse, manipulate, and extract data from JSON structures.
JSON_VALUE ( <json_string>, <json_path> ) / JSON_EXTRACT ( <json_string>, <json_path> )
Retrieves a JSON value corresponding to the given json path. If the json path is not present then None
is returned.
Note: Given JSON string should be valid
> SELECT
JSON_VALUE('{"fruits": [{"apples": 5, "oranges": 10},
{"apples": 2, "oranges": 4}],
"vegetables": [{"lettuce": 7, "kale": 8}]}','$.fruits') AS string_array
[{"apples":5,"oranges":10},{"apples":2,"oranges":4}]
> SELECT
JSON_VALUE('{"fruits": [{"apples": 5, "oranges": 10},
{"apples": 2, "oranges": 4}],
"vegetables": [{"lettuce": 7, "kale": 8}]}','$.fruits[1].oranges')
AS string_array
10
Indexing starts from 1 (i.e., the first element is at index 1, the second is at index 2, and so on).
If you provide an index that is 0 or less, it wraps around and starts counting from the end of the array. For example, an index of 0 refers to the last element in the array, -1 to the second last etc.
TO_JSON ( <expr> )
Converts a STRUCT value to a string containing the JSON representation of the value.
> select (to_json(named_struct('1','xyz','2',123)));
{
"1": "xyz",
"2": 123
}
Last updated