How to Use json_object in MySQL: Syntax and Examples

In MySQL, use json_object to create a JSON object from key-value pairs by passing keys and values as arguments. Each key must be a string, followed by its corresponding value, and the function returns a valid JSON object.

Syntax

The json_object function takes pairs of keys and values as arguments and returns a JSON object. Each key must be a string, and it is followed by its value. The syntax looks like this:

• json_object(key1, value1, key2, value2, ...)

This creates a JSON object with keys and their corresponding values.

sql

SELECT JSON_OBJECT('name', 'Alice', 'age', 30, 'city', 'New York');

Output

{"name": "Alice", "age": 30, "city": "New York"}

Example

This example shows how to create a JSON object with personal information using json_object. It returns a JSON string with keys and values you provide.

sql

SELECT JSON_OBJECT('firstName', 'John', 'lastName', 'Doe', 'isStudent', false, 'score', 85.5) AS json_result;

Output

{"firstName": "John", "lastName": "Doe", "isStudent": false, "score": 85.5}

Common Pitfalls

Common mistakes when using json_object include:

• Passing an odd number of arguments, which causes an error because keys and values must be in pairs.
• Using non-string keys, which is not allowed; keys must always be strings.
• Confusing json_object with string concatenation; it builds a JSON object, not a plain string.

Example of wrong usage and the correct way:

sql

/* Wrong: Odd number of arguments */
SELECT JSON_OBJECT('name', 'Alice', 'age');

/* Right: Even number of arguments */
SELECT JSON_OBJECT('name', 'Alice', 'age', 25);

Output

ERROR 3140 (22032): Invalid JSON text in argument 1 to function json_object: "The document is empty"

{"name": "Alice", "age": 25}

Quick Reference

Feature	Description
Function	Creates a JSON object from key-value pairs
Arguments	Pairs of keys (strings) and values
Return Type	JSON formatted string
Error	Occurs if arguments are not in pairs or keys are not strings
Use Case	Build JSON data directly in SQL queries

Key Takeaways

Use json_object with pairs of string keys and their values to create JSON objects in MySQL.

Always provide an even number of arguments; keys and values must come in pairs.

Keys must be strings; values can be strings, numbers, booleans, or NULL.

json_object returns a JSON-formatted string representing the object.

Avoid mixing json_object with string concatenation; it builds structured JSON data.