如果表中某列数据以JSON格式存储，您可以使用JSON函数查询JSON数据。Tablestore SQL的JSON函数遵循MySQL 5.7的语法。本文介绍Tablestore SQL支持的JSON函数以及JSON函数的用法。
支持的JSON函数
目前Tablestore SQL支持的JSON函数请参见下表。
JSON函数 | 说明 |
从JSON文档中解出某一路径对应的文档并取消引用转换为字符串，等效于 | |
去除JSON值外的引号，返回结果为字符串。 | |
从JSON文档中解出某一路径对应的子文档。 |
->>
->>用于从JSON文档中解出某一路径对应的文档并取消引用转换为字符串，等效于JSON_UNQUOTE(JSON_EXTRACT())。
语法
column->>path函数说明
返回值为路径参数匹配的值。
如果任何参数为NULL或所设置的路径在文档中未找到值，则返回NULL。
参数
参数 | 类型 | 说明 |
column | String | 列名。 |
path | String | 用于定位JSON文档中的某个路径。 path必须以 |
示例
以下示例用于查询json_table表pkint主键值为1的行对应coljson列值中路径为$.a的数据。
SELECT coljson, coljson->>'$.a' AS subdoc FROM json_table WHERE pkint = 1;返回结果示例如下图所示。
JSON_UNQUOTE
JSON_UNQUOTE用于去除JSON值外的引号，返回结果为字符串。
语法
JSON_UNQUOTE(json_val)函数说明
返回值为去除JSON值外面引号的字符串。
如果参数为NULL，则返回NULL。
如果值以双引号开头和结尾，但不是有效的JSON字符串文字，则会发生错误。
参数
参数 | 说明 |
json_val | JSON_EXTRACT语句。更多信息，请参见JSON_EXTRACT。 |
示例
以下示例用于查询json_table表pkint主键值为1的行对应coljson列值中路径为$.a的数据。
SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a')) AS subdoc FROM json_table WHERE pkint = 1;返回结果示例如下图所示。
JSON_EXTRACT
JSON_EXTRACT用于从JSON文档中解出某一路径对应的子文档。由于表格存储未支持原生的JSON类型，直接使用会抛出非法类型参数的错误，因此JSON_EXTRACT需要结合JSON_UNQUOTE进行使用。
语法
JSON_EXTRACT(json_doc, path[, path] ...)函数说明
返回值由路径参数匹配的所有值组成。 如果这些参数可能返回多个值，则匹配的值将自动包装为一个数组，顺序与生成值的路径相对应， 否则返回值为单个匹配值。
如果任何参数为NULL或所设置的路径在文档中未找到值，则返回NULL。
参数
参数 | 类型 | 说明 |
json_doc | String | JSON格式的文档。 重要 如果json_doc参数不是有效的JSON文档或任何路径参数不是有效的路径表达式，则会发生错误。 |
path | String | 用于定位JSON文档中的某一个路径。 path必须以 |
示例
以下示例用于查询json_table表pkint主键值为1的行对应coljson列值中路径为$.a的数据。其中coljson列的值为{"a": 1, "b":2, "c":{"d":4}}。
SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a')) AS subdoc FROM json_table WHERE pkint = 1;返回结果示例如下图所示。
您也可以一次查询多个path，多个path的结果以数组格式组织。以下SQL语句示例用于查询json_table表pkint主键值为1的行对应coljson列值中路径为$.a、$.b、$.c.d的数据。其中coljson列的值为{"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;返回结果示例如下图所示。
JSON path说明
path主要用于定位JSON文档中的某一个路径。
path必须以$为开头，$表示整个JSON文档。其后可以选择添加路径选择器，选择器可以组合使用。
如果对应路径在JSON文档中不存在数据，则会返回NULL。
路径选择器
主要的路径选择器如下：
$.key用于JSON Object类型。在半角句号（.）后可以添加对应的键值，用于选中键值对应的对象，例如$.a。如果key中包含空格，则需要为key添加双引号，例如$."a b"。[N]：用于JSON Array类型，选择数组对应的下标。下标从0开始，例如$[0]、$[1]。路径也支持包含星号（*）和
**通配符，主要用法如下：.*：用于计算JSON Object中所有成员的值。[*]：用于计算JSON Array中所有元素的值。prefix**suffix：表示全部以prefix开始，以suffix结尾的路径。
示例
JSON Object查询
假设要查询的JSON Object对象如下：
{"a": 1, "f": [1, 2, 3], "c": {"d": 4}}不同路径选择器配置时的返回值说明请参见下表。
路径选择器 | 返回值 |
$ |
|
$.a | 1 |
$.c |
|
$.c.d | 4 |
$.f[1] | 2 |
对于key中含有空格的情况，假设要查询的JSON Object对象如下：
{"a fish": "shark", "a bird": "sparrow"}不同路径选择器配置时的返回值说明请参见下表。
路径选择器 | 返回值 |
$."a fish" | shark |
$."a bird" | sparrow |
JSON Array查询
假设要查询的JSON Array对象如下：
[3, {"a": [5, 6], "b": 10}, [99, 100]]不同路径选择器配置时的返回值说明请参见下表。
当返回值为非标量值时，您可以继续进行嵌套查询。例如$[1]和$[2]返回的为非标量值，可以继续使用$[1].a、$[2][0]等进行嵌套查询。
路径选择器 | 返回值 |
$[0] | 3 |
$[1] |
|
$[1].a |
|
$[1].a[1] | 6 |
$[1].b | 10 |
$[2] |
|
$[2][0] | 99 |
$[3] | NULL |
通配符查询
假设要查询的JSON对象如下：
{"a": 1, "b": 2, "c": [3, 4, 5]}不同路径选择器配置时的返回值说明请参见下表。
路径选择器 | 返回值 |
$.* |
|
$.c[*] |
|
假设要查询的JSON对象如下：
{"a": {"b": 1}, "c": {"b": 2}}设置路径选择器为$**.b时，返回值为[1, 2]。等效于（$.a.b和$.c.b）。