Tablestore:JSON functions

Supported JSON functions

The following table describes the JSON functions that are supported by Tablestore SQL.

->>

• Syntax

  column->>path

• Description

  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 the JSON document. The value must start with $, which specifies the entire JSON document. You can specify one or more path selectors in the value of the path parameter. For more information, see JSON path.

• Examples

  You can execute the following SQL statement to query the data that is located based on 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

• 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.

  Important

  If the JSON value starts and ends with double quotation marks ("), but is not a valid JSON string, an error occurs.

• Parameters

  The json_val parameter specifies 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 based on 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

Tablestore does not support the native JSON data type. If you use the native JSON data type, an error that indicates that the data type is invalid 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 must start with $, which specifies the entire JSON document. You can specify one or more path selectors in the value of the path parameter. For more information, see JSON path.

• Examples

  You can execute the following SQL statement to query the data that is located based on 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.

  

  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:

  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.

Path selectors

The following items describe common path selectors:

• $.key is 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": {"d": 4}, "f": [1, 2, 3]}
  $.a	1
  $.c	{"d": 4}
  $.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.

  Note

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

• Perform 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
  $.*	[1, 2, [3, 4, 5]]
  $.c[*]	[3, 4, 5]

  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].