The JSON_OBJECT function in MySQL is used to create a JSON object from a set of key-value pairs. This function is particularly useful when you want to generate JSON data within a SQL query, especially when working with JSON-related features introduced in MySQL 5.7 and later versions.

Syntax

Here is the syntax for the JSON_OBJECT function:

JSON_OBJECT([key1, val1[, key2, val2[, ...]]])

key1, key2, …: The keys for the JSON object.
val1, val2, …: The corresponding values for the keys.

Example

Now, let’s look at an example to better understand how to use the JSON_OBJECT function:

SELECT JSON_OBJECT(
    'name', 'John Doe',
    'age', 30,
    'city', 'New York'
) AS person_json;

In this example, the JSON_OBJECT function is used to create a JSON object with keys (‘name’, ‘age’, ‘city’) and their corresponding values (‘John Doe’, 30, ‘New York’). The result will be a single-column result set with the alias person_json and the following JSON object:

{"name": "John Doe", "age": 30, "city": "New York"}

You can use the JSON_OBJECT function in various scenarios, such as when constructing JSON arrays, combining multiple JSON objects, or as part of other JSON-related functions in MySQL.

Here’s another example where JSON_OBJECT is used in combination with other functions:

SELECT JSON_ARRAYAGG(JSON_OBJECT(
    'product_id', product_id,
    'product_name', product_name,
    'price', price
)) AS products_json
FROM products;

In this case, the JSON_OBJECT function is used to create JSON objects for each row in the ‘products’ table, and JSON_ARRAYAGG is used to aggregate these objects into a JSON array. The resulting JSON array will contain information about each product, combining the ‘product_id’, ‘product_name’, and ‘price’ fields.

The JSON_OBJECT function is a versatile feature for creating JSON objects in MySQL. It can be used to create JSON objects with any number of properties and values.