The read-only analysis instance supports the JSON (JavaScript Object Notation) data type, which complies with the RFC 7159 standard. Users can store semi-structured JSON data and access or modify data within JSON documents.
JSON documents stored in JSON columns are converted into a binary format optimized for fast reading and access. When JSON values are read, no re-parsing from text is required.
In addition to the JSON data type, the read-only analysis instance provides a set of SQL functions for creating, querying, and modifying JSON values. The specific functions supported are listed below.
JSON_OBJECT Function
Create a JSON object based on the input parameters.
Syntax: JSON_OBJECT(key1, value1, key2, value2, ...)
JSON_REMOVE Function
Remove the elements corresponding to one or more paths from a specified JSON document.
Syntax: JSON_REMOVE(json_doc, path [, path] ...)
json_doc: The JSON field.
path: The path(s) to be deleted. Multiple paths can be specified.
JSON_EXTRACT Function
Extract the element at a specified path from a JSON document. If the specified path does not exist, NULL is returned. You can specify multiple paths, and the matched values are returned as an array.
Syntax: JSON_EXTRACT(json_doc, path [, path] ...), which is equivalent to column -> path.
json_doc: The JSON field.
path: The path to be extracted.
JSON_UNQUOTE Function
Remove the quotes from a JSON value and output it as a string.
Syntax: JSON_UNQUOTE(column -> path), which is equivalent to column ->> path.
column: The JSON field.
path: The path to be extracted.
JSON_CONTAINS Function
Determine whether a specified path contains specified data. It returns 1 if the data is contained, and 0 if not. If any parameter is NULL, or the path does not exist, NULL is returned.
Syntax: JSON_CONTAINS(json_doc, candidate [, path])
json_doc: The JSON field.
candidate: The value used for evaluation.
path: The path, which is optional.
JSON_CONTAINS_PATH Function
Check whether a specified path exists in a JSON document. It returns 1 if the path exists, and 0 if it does not. If any parameter is NULL, NULL is returned.
Syntax: JSON_CONTAINS_PATH(json_doc, one_or_all, path [, path] ...)
json_doc: The JSON field.
one_or_all: The value is one or all. one indicates that 1 is returned if any one of the paths exists. all indicates that 1 is returned only if all paths exist.
path: The path, which supports specifying multiple instances.
Type Conversion
It supports converting JSON to the INT, UINT, FLOAT, STRING, DATE, DATETIME, and DURATION types.
It supports converting the INT, UINT, FLOAT, STRING, DATE, DATETIME, and DURATION types to JSON.