In MariaDB, JSON_LENGTH() is a built-in function that returns the length of a JSON document.

When you call this function, you provide the JSON document as an argument. You can also provide a path argument to return the length of a value within the document.

The length is determined as follows:

• A scalar’s length is always 1.
• If an array, the number of elements in the array.
• If an object, the number of members in the object.

The length of nested arrays or objects are not counted.

Syntax

The syntax goes like this:

JSON_LENGTH(json_doc[, path])

Where json_doc is the JSON document, and path is an optional argument that specifies a path within the document.

Example

Here’s an example to demonstrate.

SET @json_document = '
    { 
        "name": "Wag", 
        "type": "Dog", 
        "weight": 20 
    }
';
SELECT JSON_LENGTH(@json_document);

Result:

+-----------------------------+
| JSON_LENGTH(@json_document) |
+-----------------------------+
|                           3 |
+-----------------------------+

Specifying a Path

Here’s an example of specifying a path within the document:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog",
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_LENGTH(@json_document, '$.details');

Result:

+------------------------------------------+
| JSON_LENGTH(@json_document, '$.details') |
+------------------------------------------+
|                                        2 |
+------------------------------------------+

As mentioned, it doesn’t count the length of nested objects, so we get a result of 2.

The next example goes down a level and counts the length of the awards object:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog",
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_LENGTH(@json_document, '$.details.awards');

Result:

+-------------------------------------------------+
| JSON_LENGTH(@json_document, '$.details.awards') |
+-------------------------------------------------+
|                                               3 |
+-------------------------------------------------+

Scalars

A scalar’s length is always 1:

SELECT JSON_LENGTH( 
    '{ "a" : 378, "b" : 45 }', 
    '$.a' 
    ) AS Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

Arrays

If the document is an array, JSON_LENGTH() counts the number of elements in the array:

SET @json_document = '
    { 
        "name": "Wag", 
        "type": "Dog", 
        "scores": [9, 7, 8, 10, 3] 
    }
';
SELECT JSON_LENGTH(@json_document, '$.scores');

Result:

+-----------------------------------------+
| JSON_LENGTH(@json_document, '$.scores') |
+-----------------------------------------+
|                                       5 |
+-----------------------------------------+

Non-Existent Paths

Passing a path that doesn’t exist in the JSON document results in NULL.

Example:

SET @json_document = '
    { 
        "name": "Wag", 
        "type": "Dog"
    }
';
SELECT JSON_LENGTH(@json_document, '$.oops');

Result:

+---------------------------------------+
| JSON_LENGTH(@json_document, '$.oops') |
+---------------------------------------+
|                                  NULL |
+---------------------------------------+

Empty Objects

If the selected object is empty, the result is 0:

SELECT JSON_LENGTH('{}');

Result:

+-------------------+
| JSON_LENGTH('{}') |
+-------------------+
|                 0 |
+-------------------+

Null Arguments

If any argument is NULL, the result is NULL:

SELECT 
    JSON_LENGTH(null) AS a,
    JSON_LENGTH(null, '$.type') AS b,
    JSON_LENGTH('{"a":1}', null) AS c;

Result:

+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_LENGTH();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_LENGTH'