JSON query and processing
Arrow function
Queries an element that can be located by the jsonpath expression in a JSON object and returns a JSON value. The arrow function -> is more compact and easier to use than the jsonquery function.
cast
Converts a value between the JSON type and SQL type.
get_json_bool
Parses and gets the boolean value from a specified JSON path in a JSON string.
get_json_double
This function analyzes and gets the floating point value from a specified path in json string. json_path must start with $ and use . as the path separator.
get_json_int
This function analyzes and gets the integer value from a specified path in json string.
get_json_string,get_json_object
Analyzes and gets strings from the specified path (jsonpath) in a JSON string. If the format of jsonstring or json_path is wrong or if no matching value is found, this function will return NULL.
json_contains
Checks whether a JSON document contains a specific value or subdocument. If the target JSON document contains the candidate JSON value, the JSONCONTAINS function returns 1. Otherwise, the JSONCONTAINS function returns 0.
json_each
Expands the outermost elements of a JSON object into a set of key-value pairs held in two columns and returns a table that consists of one row for each element.
json_exists
Checks whether a JSON object contains an element that can be located by the jsonpath expression. If the element exists, the JSONEXISTS function returns 1. Otherwise, the JSON_EXISTS function returns 0.
json_keys
Returns the top-level keys from a JSON object as a JSON array, or, if a path is specified, the top-level keys from the path.
json_length
Returns the length of a JSON document. If a path is specified, this function returns the length of the value identified by the path.
json_query
Queries the value of an element that can be located by the json_path expression in a JSON object and returns a JSON value.
json_remove
Removes data from a JSON document at one or more specified JSON paths and returns the modified JSON document.
json_string
Converting JSON object to JSON string
to_json
Converts a Map or Struct value into a JSON string. If the input value is NULL, NULL is returned.