Converting Row Data to JSON in MySQL

Convert Row Data to JSON Using JSON_OBJECT

Write a MySQL query to convert relational row data into a JSON object using the JSON_OBJECT function.

Solution:

-- Convert product details from separate columns into a JSON object.

-- Select the ProductID and create a JSON object from the product details.
SELECT 
    -- Retrieve the ProductID column.
    ProductID, 
    
    -- Use JSON_OBJECT to create a JSON object from the specified columns.
    -- Each key-value pair in the JSON object corresponds to a column name and its value.
    JSON_OBJECT(
        'ProductName', ProductName,  -- Include the ProductName column as a key-value pair.
        'Price', Price,              -- Include the Price column as a key-value pair.
        'InStock', InStock            -- Include the InStock column as a key-value pair.
    ) AS ProductJSON  -- Alias the resulting JSON object as ProductJSON.

-- Specify the table from which to retrieve the data.
FROM Products;

Explanation:

• Purpose of the Query:
• To transform individual column data into a structured JSON object.
• Useful for applications that consume JSON data formats.
• Key Components:
• JSON_OBJECT('ProductName', ProductName, 'Price', Price, 'InStock', InStock) : Constructs a JSON object with key-value pairs from row data.
• AS ProductJSON : Provides an alias for the output column.
• Real-World Application:
• Ideal for APIs and services that deliver data in JSON format to client applications.

Notes:

• This technique facilitates the conversion of traditional relational data into modern JSON responses.
• JSON_OBJECT can be extended to include more columns as needed.

For more Practice: Solve these Related Problems:

• Write a MySQL query to convert several columns from a table row into a single JSON object.
• Write a MySQL query to create a nested JSON object from row data by grouping related columns.
• Write a MySQL query to convert row data to JSON while replacing column names with custom key names.
• Write a MySQL query to convert row data to a JSON object, ensuring that NULL values are explicitly represented.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Create a Generated Column from JSON Data.
Next MySQL Exercise: Aggregate Data into a JSON Array with JSON_ARRAYAGG.