You can use JSON functions to query data that is stored in the JSON format in a column of a table. The JSON functions that are supported by Tablestore SQL use the MySQL 5.7 syntax. This topic describes the JSON functions that are supported by Tablestore SQL and how to use the JSON functions.
Supported JSON functions
The following table describes the JSON functions that are supported by Tablestore SQL.
JSON function | Description |
Returns the data based on the path that you specify from a JSON document after the result is unquoted and converted into a string. This function is equivalent to | |
Unquotes a JSON value and returns the result as a string. | |
Returns data from a JSON document. The data that is returned is located based on the path that you specify as an argument. |
->>
The ->> function returns the data based on the path that you specify from a JSON document after the result is unquoted and converted into a string. This function is equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
Syntax
column->>pathDescription
The return value is the value that is located based on the path parameter that you specify.
If the value of a parameter is NULL or the path that is specified by the path parameter is not found in the document, NULL is returned.
Parameters
Parameter | Type | Description |
column | String | The name of the column. |
path | String | The path of a JSON document. The value of the path parameter must start with |
Examples
You can execute the following SQL statement to query the data that is located by the $.a path in the coljson column from the row in which the value of the pkint primary key column is 1 in the json_table table:
SELECT coljson, coljson->>'$.a' AS subdoc FROM json_table WHERE pkint = 1;The following figure shows a sample output.
JSON_UNQUOTE
The JSON_UNQUOTE function unquotes a JSON value and returns the result as a string.
Syntax
JSON_UNQUOTE(json_val)Description
The return value is the value that is obtained after the JSON value is unquoted and converted into a string.
If the value of the json_val parameter is NULL, NULL is returned.
If the JSON value starts and ends with double quotation marks ("), but is not a valid JSON string, an error occurs.
Parameters
Parameter | Description |
json_val | The JSON_EXTRACT statement. For more information, see JSON_EXTRACT. |
Examples
You can execute the following SQL statement to query the data that is located by the $.a path in the coljson column from the row in which the value of the pkint primary key column is 1 in the json_table table:
SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a')) AS subdoc FROM json_table WHERE pkint = 1;The following figure shows a sample output.
JSON_EXTRACT
The JSON_EXTRACT function returns data from a JSON document. The data that is returned is located based on the path that you specify as an argument. Tablestore does not support the native JSON data type. If you use the JSON data type, an error that indicates invalid data type is returned. Therefore, you must use JSON_EXTRACT together with JSON_UNQUOTE.
Syntax
JSON_EXTRACT(json_doc, path[, path] ...)Description
The return value consists of all values that are located based on the paths specified by the path parameter. If multiple values are located based on the paths that are specified by the path parameter, the values are automatically wrapped into an array based on the order in which the paths are specified by the path parameter. If only one value is located based on the path that is specified by the path parameter, the value is returned.
If the value of a parameter is NULL or the path that is specified by the path parameter is not found in the document, NULL is returned.
Parameters
Parameter | Type | Description |
json_doc | String | The JSON document. Important If the JSON document that is specified by the json_doc parameter is invalid or the path expression that is specified by the path parameter is invalid, an error occurs. |
path | String | The path of the JSON document. The value of the path parameter must start with |
Examples
You can execute the following SQL statement to query the data that is located by the $.a path in the coljson column from the row in which the value of the pkint primary key column is 1 in the json_table table: In this example, the value of the coljson column is {"a": 1, "b":2, "c":{"d":4}}.
SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a')) AS subdoc FROM json_table WHERE pkint = 1;The following figure shows a sample output.
You can also specify multiple paths at the same time. The return value is in the array format. For example, you can execute the following SQL statement to query the data that is located based on the $.a, $.b, and $.c.d paths in the coljson column from the row in which the value of the pkint primary key column is 1 in the json_table table: In this example, the value of the coljson column is {"a": 1, "b":2, "c":{"d":4}}.
SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a', '$.b', '$.c.d')) AS subdoc FROM json_table WHERE pkint = 1;The following figure shows a sample output.
JSON path
The path parameter is used to locate a path of a JSON document.
The value of the path parameter must start with $, which specifies the entire JSON document. You can specify one or more path selectors in the value of the path parameter.
If no data is located based on the path in the JSON document, NULL is returned.
Path selectors
The following items describe common path selectors:
$.keyis used to select JSON objects. You can add a key after the period (.) to select the object that corresponds to the key, such as$.a. If the specified key contains spaces, enclose the key in double quotation marks ("), such as$."a b".[N]is used to select the subscript of JSON arrays. Subscripts start from 0. Examples:$[0]and$[1].The path can contain an asterisk (*) and the
**wildcard character. The following items describe how you can use the path that contains an asterisk (*) and the ** wildcard character:.*: used to calculate the values of all members in a JSON object.[*]: used to calculate the values of all elements in a JSON array.prefix**suffix: used to specify all paths that start with a specific prefix and end with a specific suffix.
Examples
Query JSON objects
For example, you want to query the following JSON object:
{"a": 1, "f": [1, 2, 3], "c": {"d": 4}}The following table describes the return values when different path selectors are configured.
Path selector | Return value |
$ |
|
$.a | 1 |
$.c |
|
$.c.d | 4 |
$.f[1] | 2 |
For example, you want to query the following JSON object in which the key contains a space:
{"a fish": "shark", "a bird": "sparrow"}The following table describes the return values when different path selectors are configured.
Path selector | Return value |
$."a fish" | shark |
$."a bird" | sparrow |
Query JSON arrays
For example, you want to query the following JSON array:
[3, {"a": [5, 6], "b": 10}, [99, 100]]The following table describes the return values when different path selectors are configured.
If the return value is a non-scalar value, you can proceed with nested queries. For example, the values that are returned for $[1] and $[2] are non-scalar values. In this case, you can use $[1].a and $[2][0] to perform nested queries.
Path selector | Return value |
$[0] | 3 |
$[1] |
|
$[1].a |
|
$[1].a[1] | 6 |
$[1].b | 10 |
$[2] |
|
$[2][0] | 99 |
$[3] | NULL |
Perform a wildcard query
For example, you want to query the following JSON object:
{"a": 1, "b": 2, "c": [3, 4, 5]}The following table describes the return values when different path selectors are configured.
Path selector | Return value |
$.* |
|
$.c[*] |
|
For example, you want to query the following JSON object:
{"a": {"b": 1}, "c": {"b": 2}}If you set the path selector to $**.b, which is equivalent to $.a.b and $.c.b in this example, the return value is [1, 2].