How to Use json_array in MySQL: Syntax and Examples

In MySQL, use the json_array() function to create a JSON array from one or more values. It returns a JSON array containing the given arguments, which can be strings, numbers, or other JSON values.

Syntax

The json_array() function takes one or more values as arguments and returns a JSON array containing those values.

• json_array(value1, value2, ...): Creates a JSON array with the given values.
• Values can be strings, numbers, or JSON expressions.
• Returns a JSON type result.

sql

SELECT JSON_ARRAY('apple', 'banana', 'cherry');

Output

["apple", "banana", "cherry"]

Example

This example shows how to create a JSON array from multiple values and how to use it in a SELECT statement.

sql

SELECT JSON_ARRAY(1, 'two', 3.0, JSON_OBJECT('key', 'value')) AS my_json_array;

Output

[1, "two", 3.0, {"key": "value"}]

Common Pitfalls

Common mistakes when using json_array() include:

• Passing NULL values without handling them, which results in null elements in the array.
• Confusing json_array() with string concatenation; it creates JSON arrays, not strings.
• Not using valid JSON expressions inside the function.

Example of wrong and right usage:

sql

/* Wrong: Passing NULL without handling */
SELECT JSON_ARRAY('apple', NULL, 'cherry');

/* Right: Using IFNULL to replace NULL */
SELECT JSON_ARRAY('apple', IFNULL(NULL, 'default'), 'cherry');

Output

["apple", null, "cherry"]
["apple", "default", "cherry"]

Quick Reference

Function	Description	Example
json_array(value1, value2, ...)	Creates a JSON array from values	json_array('a', 'b', 'c') → ["a", "b", "c"]
json_object(key, value, ...)	Creates a JSON object	json_object('id', 1, 'name', 'John') → {"id":1,"name":"John"}
ifnull(expr, alt)	Replaces NULL with alternate value	ifnull(NULL, 'default') → 'default'

Key Takeaways

Use json_array() to create JSON arrays from multiple values in MySQL.

Values can be strings, numbers, or JSON expressions inside json_array().

Handle NULL values carefully to avoid unexpected null elements in arrays.

json_array() returns a JSON type, not a plain string.

Combine json_array() with other JSON functions like json_object() for complex JSON data.