In 2.2410.x and later versions, the read-only analysis engine adds support for JSON (JavaScript Object Notation) data types that conform to RFC 7159 standard. Users can save semi-structured JSON data and can access and modify data in JSON documents.
JSON documents stored in JSON columns can be converted into a format that is easy to read and access. When the server needs to read a JSON value stored in binary format, it does not need to parse the value from text.
Besides JSON data types, there is a set of SQL functions that can be used to create, search, and perform other operations on JSON values. The supported functions are as follows:
JSON_OBJECT Function
Create a JSON object.
Syntax: JSON_OBJECT(key1, value1, key2, value2…)
Create a JSON object based on the input parameter value.
JSON_REMOVE Function
Delete the corresponding path from the specified JSON object.
Syntax: JSON_REMOVE(json_doc, path[, path] ...)
json_doc is a JSON field, and path is a path. Multiple paths are supported.
JSON_EXTRACT Function
Extract elements at the specified path from a JSON field. If the specified path does not exist, NULL is returned. Multiple paths can be specified, and multiple matched values will be returned as an array.
Syntax: JSON_EXTRACT(json_doc, path[, path] ...) is synonymous with column-> path.
json_doc is a JSON field, and path is a path.
JSON_UNQUOTE Function
Escape JSON and output it as a string.
Syntax: JSON_UNQUOTE(column -> path) is synonymous with column->> path.
json_doc is a JSON field, and path is a path.
JSON_CONTAINS Function
Specify whether the path contains the specified data. If it does, 1 is returned; if not, 0 is returned. If any parameter is NULL or the path does not exist, NULL is returned.
Syntax: JSON_CONTAINS(json_doc, candidate[, path])
json_doc is a JSON field, candidate is a judgment value, and path is a path.
JSON_CONTAINS_PATH Function
Check whether a specified path exists and whether one or all conditions are met. If it exists, 1 is returned; if not, 0 is returned. If any parameter is NULL, NULL is returned.
Syntax: JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
json_doc is a JSON field. In one_or_all, one means return once one is queried, and all means return after all are queried. path is a path. Multiple paths are supported.
Type Conversion
Support conversion from JSON to INT, UINT, FLOAT, STRING, DATE, DATETIME, and DURATION.
Support conversion from INT, UINT, FLOAT, STRING, DATE, DATETIME, and DURATION to JSON.