JSON函数

支持的JSON函数

目前Tablestore SQL支持的JSON函数请参见下表。

->>

• 语法

  column->>path

• 函数说明

  返回值为路径参数匹配的值。

  如果任何参数为NULL或没有路径在文档中找到值，则返回NULL。

• 参数

  参数	类型	说明
  column	String	列名。
  path	String	用于定位JSON文档中的某一个路径。 path必须以$为开头，$表示整个JSON文档。其后可以选择添加路径选择器，选择器可以组合进行使用。更多信息，请参见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_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类型，直接使用会抛非法类型参数的错误，因此JSON_EXTRACT需要结合JSON_UNQUOTE进行使用。

• 语法

  JSON_EXTRACT(json_doc, path[, path] ...)

• 函数说明

  返回值由路径参数匹配的所有值组成。 如果这些参数可能返回多个值，则匹配的值将自动包装为一个数组，顺序与生成值的路径相对应， 否则返回值为单个匹配值。

  如果任何参数为NULL或没有路径在文档中找到值，则返回NULL。

• 参数

  参数	类型	说明
  json_doc	String	JSON格式的文档。 重要 如果json_doc参数不是有效的JSON文档或任何路径参数不是有效的路径表达式，则会发生错误。
  path	String	用于定位JSON文档中的某一个路径。 path必须以$为开头，$表示整个JSON文档。其后可以选择添加路径选择器，选择器可以组合进行使用。更多信息，请参见JSON path说明。

• 示例

  查询json_table表pkint主键值为1的行对应coljson列值中路径为$.a的数据。

  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的数据。

  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文档。其后可以选择添加路径选择器，选择器可以组合进行使用。

路径选择器

主要的路径选择器如下：

• $.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": {"d": 4}, "f": [1, 2, 3]}
  $.a	1
  $.c	{"d": 4}
  $.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]	{"a": [5, 6], "b": 10}
  $[1].a	[5, 6]
  $[1].a[1]	6
  $[1].b	10
  $[2]	[99, 100]
  $[2][0]	99
  $[3]	NULL

• 通配符查询

  假设要查询的JSON对象如下：

  {"a": 1, "b": 2, "c": [3, 4, 5]}

  不同路径选择器配置时的返回值说明请参见下表。

  路径选择器	返回值
  $.*	[1, 2, [3, 4, 5]]
  $.c[*]	[3, 4, 5]

  假设要查询的JSON对象如下：

  {"a": {"b": 1}, "c": {"b": 2}}

  设置路径选择器为$**.b时，返回值为[1, 2]。等效于（$.a.b和$.c.b）。