JSON Functions

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
}