Purpose
This function returns the length of a JSON document. If the path parameter is specified, it returns the length of the value identified by the path.
Syntax
JSON_LENGTH(json_doc[, path])
Considerations
The json_doc parameter specifies the JSON document, and the path parameter specifies the path. If any parameter is NULL or the path parameter does not identify a value in the document, NULL is returned.
An error is returned in the following cases:
The
json_docparameter is not a valid JSON document.The
pathparameter is not a valid path expression.The
pathparameter contains the * or ** wildcard.
The length of a JSON document is defined as follows:
The length of a scalar is 1.
The length of an array is the number of elements in the array.
The length of an object is the number of members in the object.
The length of nested arrays or objects is not counted.
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