Purpose
This function returns the length of a JSON document. If the path parameter is provided, it returns the length of the value identified by the path in the document.
Syntax
JSON_LENGTH(json_doc[, path])
Description
The json_doc parameter specifies the JSON document, and path is a path parameter. If either parameter is NULL or the path parameter does not identify a value in the document, NULL is returned.
An error will be returned in the following scenarios:
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 its elements.
The length of an object is the number of its members.
The length of nested arrays or objects is 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