Purpose
JSON_LENGTH() returns the length of a JSON document. If the path parameter is specified, this function returns the length of the value in the document identified by the path.
Syntax
JSON_LENGTH(json_doc[, path])
Description
json_doc specifies the name of the JSON document, and path specifies the path. If any argument is NULL or path does not identify any value in the document, the return value is NULL.
An error occurs in the following cases:
The JSON document specified for
json_docis invalid.The path expression specified for
pathis invalid.The specified
pathcontains a * or ** wildcard.
The length of a JSON document is defined as follows:
The length of a scalar value is 1.
The length of an array is equal to the number of elements in the array.
The length of an object is equal to the number of object members.
Lengths of nested arrays or objects are not calculated.
Examples
obclient> SELECT JSON_LENGTH('[1, 2, {"a": 3}, 4]');
+------------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}, 4]') |
+------------------------------------+
| 4 |
+------------------------------------+
1 row in set
obclient> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 303}}');
+------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 303}}') |
+------------------------------------------+
| 2 |
+------------------------------------------+
1 row in set
obclient> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
1 row in set