SQL
Construct and Query JSON Data in MySQL
Master MySQL's JSON functions like `JSON_OBJECT`, `JSON_ARRAYAGG`, and `JSON_EXTRACT` to dynamically create, manipulate, and query JSON data directly within your database tables.
-- Example 1: Constructing a JSON object from columns
SELECT
id,
name,
JSON_OBJECT(
'email', email,
'phone', phone_number,
'address', JSON_OBJECT('street', street, 'city', city, 'zip', zip_code)
) AS contact_info
FROM
users
WHERE
id = 1;
-- Example 2: Aggregating multiple rows into a JSON array
SELECT
product_category,
JSON_ARRAYAGG(
JSON_OBJECT('product_name', product_name, 'price', price)
) AS products_in_category
FROM
products
GROUP BY
product_category;
-- Example 3: Querying values from a JSON column (assuming 'details' is a JSON column)
SELECT
item_id,
JSON_EXTRACT(details, '$.color') AS item_color,
JSON_EXTRACT(details, '$.dimensions.width') AS item_width
FROM
inventory
WHERE
JSON_EXTRACT(details, '$.status') = 'available';
How it works: MySQL provides a rich set of functions for working with JSON data types. This snippet demonstrates three key uses: `JSON_OBJECT()` constructs a JSON object from key-value pairs, useful for consolidating related column data. `JSON_ARRAYAGG()` aggregates multiple rows into a single JSON array, which is great for hierarchical data representation. Finally, `JSON_EXTRACT()` allows you to parse and retrieve specific values from a JSON column using JSON path expressions (e.g., `$.color` or `$.dimensions.width`), enabling flexible data storage and retrieval within your relational database.