How to Use json_length in MySQL: Syntax and Examples

In MySQL, use the json_length() function to count the number of elements in a JSON array or the number of keys in a JSON object. It takes a JSON document as input and returns an integer representing the count. You can optionally specify a path to count elements inside nested JSON structures.

Syntax

The json_length() function syntax is:

• json_length(json_doc): Counts elements in the whole JSON document.
• json_length(json_doc, path): Counts elements at the specified JSON path.

json_doc is the JSON data (string or column). path is an optional string to specify a location inside the JSON, using JSON path syntax.

sql

json_length(json_doc)
json_length(json_doc, path)

Example

This example shows how to count elements in a JSON array and keys in a JSON object using json_length().

sql

SELECT
  json_length('["apple", "banana", "cherry"]') AS array_count,
  json_length('{"name": "John", "age": 30, "city": "NY"}') AS object_count,
  json_length('{"fruits": ["apple", "banana", "cherry"]}', '$.fruits') AS nested_array_count;

Output

array_count | object_count | nested_array_count
------------|--------------|-------------------
3           | 3            | 3

Common Pitfalls

Common mistakes when using json_length() include:

• Passing invalid JSON causes errors.
• Using incorrect JSON path syntax returns NULL.
• Expecting json_length() to count nested elements without specifying the correct path.

Always validate JSON and use the correct path to get accurate counts.

sql

/* Wrong: invalid JSON string */
SELECT json_length('invalid json');

/* Right: valid JSON string */
SELECT json_length('[1, 2, 3]');

/* Wrong: incorrect path returns NULL */
SELECT json_length('{"a": [1,2]}', '$.wrongpath');

/* Right: correct path */
SELECT json_length('{"a": [1,2]}', '$.a');

Output

ERROR 3140 (22032): Invalid JSON text

3

NULL

2

Quick Reference

Usage	Description
json_length(json_doc)	Count elements in entire JSON document
json_length(json_doc, path)	Count elements at specified JSON path
json_length('[]')	Returns 0 for empty array
json_length('{}')	Returns 0 for empty object
json_length(invalid_json)	Causes error

Key Takeaways

Use json_length() to count elements or keys in JSON data in MySQL.

You can specify a JSON path to count elements inside nested structures.

Invalid JSON or wrong paths cause errors or NULL results.

json_length() returns an integer count or NULL if path is invalid.

Always validate JSON input before using json_length().