JSON_LENGTH
Description
The JSON_LENGTH function returns the length or number of elements of a given JSON document. If the JSON document is an array, the number of elements in the array is returned; if the JSON document is an object, the number of key-value pairs in the object is returned. Returns NULL if the JSON document is empty or invalid.
Syntax
JSON_LENGTH(<json_str> [ , <json_path> ])
Required Parameters
| parameters | described |
|---|---|
<json_str> | The length of the JSON string needs to be checked. |
Optional Parameters
| parameters | described |
|---|---|
<json_path> | If a path is specified, the JSON_LENGTH() function returns the length of the data that matches the path in the JSON document, otherwise it returns the length of the JSON document |
Usage Notes
This function calculates the length of a JSON document based on the following rules:
- The length of the scalar is 1. For example: '1','"x "','true',' false', and 'null' are all of length 1.
- The length of an array is the number of array elements. For example: '[1,2]' has length 2.
- The length of an object is the number of object members. For example: '{"x": 1}' has length 1
Return Value
- For a JSON array, returns the number of elements in the array.
- For JSON objects, returns the number of key-value pairs in the object.
- Returns NULL for invalid JSON strings.
- For other types (such as strings, numbers, booleans, null, etc.), NULL is returned.
Examples
SELECT json_length('{"k1":"v31","k2":300}');
+--------------------------------------+
| json_length('{"k1":"v31","k2":300}') |
+--------------------------------------+
| 2 |
+--------------------------------------+
SELECT json_length('"abc"');
+----------------------+
| json_length('"abc"') |
+----------------------+
| 1 |
+----------------------+
SELECT json_length('{"x": 1, "y": [1, 2]}', '$.y');
+---------------------------------------------+
| json_length('{"x": 1, "y": [1, 2]}', '$.y') |
+---------------------------------------------+
| 2 |
+---------------------------------------------+
