JSON_LENGTH()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns the length for an item of JSON data.
USAGE
JSON_LENGTH(json_data[, path])
Argument Name | Description |
|---|---|
| The JSON data to measure |
| Optional. A path expression specifying what part of the data to measure |
DETAILS
JSON_LENGTH() is a JSON function that finds the number values in valid JSON data.
JSON data is valid if it conforms to ECMA-404: The JSON Data Interchange Standard.
The length is computed as follows:
A JSON array returns the count of items in the top-level array
A JSON object returns the count of keys in the top-level object
A JSON scalar returns a
1
A NULL is returned if one of the arguments is NULL or the path does not exist in the JSON data.
EXAMPLES
With Literal Values
In the following example, JSON_LENGTH() is called with a JSON literal value. The result demonstrates how to obtain the length of a JSON value:
SELECT JSON_LENGTH('[1, 2, 3, 4]') AS array,
JSON_LENGTH('{"foo": 1, "bar": 2}') AS obj,
JSON_LENGTH('"hello there"') AS scalar;
+-------+------+--------+
| array | obj | scalar |
+-------+------+--------+
| 4 | 2 | 1 |
+-------+------+--------+
Example Schema and Data
Some of the examples are based on the contacts table:
CREATE TABLE contacts (
contact JSON
);
INSERT INTO contacts(contact) VALUES
('{"id": 1, "email": "john.smith@example.com", "name1": "John", "name2": "Smith", "is_new": false}'),
('{"id": 2, "email": "jon.smith@example.com", "name1": "Jon", "name2": "Smith"}'),
('{"email": "johnny.smith@example.com", "name1": "Johnny", "name2": "Smith"}');
MariaDB even supports measuring scalar and NULL JSON values:
INSERT INTO contacts VALUES
(1),
(TRUE),
('"a"'),
(NULL);
Per-row Values
This example returns the length of each row's JSON data:
SELECT contact,
JSON_LENGTH(contact) AS result
FROM contacts\G
*************************** 1. row ***************************
contact: {"id": 1, "email": "john.smith@example.com", "name1": "John", "name2": "Smith", "is_new": false}
result: 5
*************************** 2. row ***************************
contact: {"id": 2, "email": "jon.smith@example.com", "name1": "Jon", "name2": "Smith"}
result: 4
*************************** 3. row ***************************
contact: {"email": "johnny.smith@example.com", "name1": "Johnny", "name2": "Smith"}
result: 3
*************************** 4. row ***************************
contact: 1
result: 1
*************************** 5. row ***************************
contact: true
result: 1
*************************** 6. row ***************************
contact: "a"
result: 1
*************************** 7. row ***************************
contact: NULL
result: NULL
JSON Length at Specific Path
JSON_LENGTH() may be used to find JSON length at specific path in a JSON document by supplying the optional second argument for path:
SELECT JSON_LENGTH('{"id": 1,
"email": ["john.smith@example.com","johnsmith@example.com"],
"name1": "John",
"name2": "Smith",
"is_new": false}','$.email') AS email_len;
+-----------+
| email_len |
+-----------+
| 2 |
+-----------+