tencent cloud

JSON Function
Download PDF
Last updated:2025-05-09 11:51:19
JSON Function
Last updated: 2025-05-09 11:51:19
Download PDF
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.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback