MySQL JSON Examples: Build API Responses, Store Events

These recipes show how JSON fits into real MySQL work. Each is small, and each has been verified.

Pattern 1: build a nested API response in SQL

The task: return customers with their orders as one JSON document, ready to send to a client.

query.sql

SQL

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, city VARCHAR(20) NOT NULL);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, total DECIMAL(8,2) NOT NULL, status VARCHAR(10) NOT NULL);
INSERT INTO customers VALUES (1, 'Maya', 'Pune'), (2, 'Rohan', 'Delhi'), (3, 'Tara', 'Kochi');
INSERT INTO orders VALUES (1, 1, 680.00, 'paid'), (2, 1, 1299.00, 'paid'), (3, 2, 145.00, 'shipped');

SELECT JSON_PRETTY(
    JSON_OBJECT(
        'id', c.id,
        'name', c.name,
        'orders', COALESCE(
            (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', o.id, 'total', o.total, 'status', o.status))
             FROM orders o WHERE o.customer_id = c.id),
            JSON_ARRAY())
    )
) AS customer_json
FROM customers c
WHERE c.id = 1;

Output

OUTPUT

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer_json                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "id": 1,
  "name": "Maya",
  "orders": [
    {
      "id": 1,
      "total": 680.00,
      "status": "paid"
    },
    {
      "id": 2,
      "total": 1299.00,
      "status": "paid"
    }
  ]
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

A correlated subquery builds the list of orders per customer. COALESCE(..., JSON_ARRAY()) gives an empty array for customers without orders (Tara), instead of NULL.

For the whole list in one document:

query.sql

SQL

1
2

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', c.id, 'name', c.name, 'order_count', (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id))) AS all_customers
FROM customers c;

Output

OUTPUT

+------------------------------------------------------------------------------------------------------------------------------------------+
| all_customers                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id": 1, "name": "Maya", "order_count": 2}, {"id": 2, "name": "Rohan", "order_count": 1}, {"id": 3, "name": "Tara", "order_count": 0}] |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

The same query can feed an endpoint directly. For large results, build the JSON in the application, since a single JSON value is limited by max_allowed_packet and by memory.

Pattern 2: store events with the raw payload and extracted columns

Keep the original message in JSON (for audit and replay), and copy the fields you query into real, indexed columns:

query.sql

SQL

CREATE TABLE webhook_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payload JSON NOT NULL,
    event_type VARCHAR(30) GENERATED ALWAYS AS (payload->>'$.type') STORED,
    order_ref VARCHAR(20) GENERATED ALWAYS AS (payload->>'$.data.order') STORED,
    KEY idx_type_received (event_type, received_at),
    KEY idx_order (order_ref)
);

INSERT INTO webhook_events (payload) VALUES
    ('{"type": "payment.succeeded", "data": {"order": "ORD-1001", "amount": 4999}}'),
    ('{"type": "payment.failed",    "data": {"order": "ORD-1002", "amount": 899, "reason": "card_declined"}}'),
    ('{"type": "payment.succeeded", "data": {"order": "ORD-1003", "amount": 150}}');

SELECT event_type, COUNT(*) AS events, SUM(JSON_VALUE(payload, '$.data.amount' RETURNING UNSIGNED)) AS total_amount
FROM webhook_events
GROUP BY event_type
ORDER BY event_type;

Output

OUTPUT

+-------------------+--------+--------------+
| event_type        | events | total_amount |
+-------------------+--------+--------------+
| payment.failed    |      1 |          899 |
| payment.succeeded |      2 |         5149 |
+-------------------+--------+--------------+
2 rows in set

The payload keeps everything the provider sent (including fields you did not anticipate). The generated columns make event_type and order_ref fast to filter. If the provider adds new fields, nothing changes in your schema.

Pattern 3: user settings with defaults

Store only the settings a user has changed, and merge them over the defaults when you read:

query.sql

SQL

CREATE TABLE user_settings (user_id INT PRIMARY KEY, overrides JSON NOT NULL DEFAULT (JSON_OBJECT()));
INSERT INTO user_settings VALUES (1, '{"theme": "dark"}'), (2, '{}');

SET @defaults = '{"theme": "light", "language": "en", "notifications": {"email": true, "sms": false}}';

SELECT user_id, JSON_MERGE_PATCH(@defaults, overrides) AS effective_settings FROM user_settings ORDER BY user_id;

Output

OUTPUT

+---------+--------------------------------------------------------------------------------------+
| user_id | effective_settings                                                                   |
+---------+--------------------------------------------------------------------------------------+
|       1 | {"theme": "dark", "language": "en", "notifications": {"sms": false, "email": true}}  |
|       2 | {"theme": "light", "language": "en", "notifications": {"sms": false, "email": true}} |
+---------+--------------------------------------------------------------------------------------+
2 rows in set

User 1 overrides the theme, and user 2 gets all the defaults. When you change a default later, every user who did not override it gets the new value. Update one user’s override with JSON_SET (Modifying JSON). User 2’s overrides are empty ({}), so there is no notifications object yet and JSON_SET cannot create a nested path. We set the whole notifications object:

query.sql

SQL

1
2

UPDATE user_settings SET overrides = JSON_SET(overrides, '$.notifications', JSON_OBJECT('sms', true)) WHERE user_id = 2;
SELECT JSON_EXTRACT(JSON_MERGE_PATCH(@defaults, overrides), '$.notifications') AS notifications FROM user_settings WHERE user_id = 2;

Output

OUTPUT

+------------------------------+
| notifications                |
+------------------------------+
| {"sms": true, "email": true} |
+------------------------------+
1 row in set

(JSON_MERGE_PATCH merges nested objects, so email stays true from the defaults while sms is overridden. The -> operator works only on a column, so a function result is read with JSON_EXTRACT.)

Pattern 4: snapshot the data at the time of an event

An order must keep the price and address as they were at checkout, even when the product catalogue or the customer’s address changes later. A JSON snapshot is a natural fit:

query.sql

SQL

CREATE TABLE sales_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    snapshot JSON NOT NULL
);

INSERT INTO sales_orders (customer_id, total, snapshot)
SELECT c.id, 80 * 2 + 15.5 * 10,
       JSON_OBJECT('customer', JSON_OBJECT('name', c.name, 'city', c.city),
                   'lines', JSON_ARRAY(JSON_OBJECT('sku', 'NB-01', 'qty', 2, 'unit_price', 80),
                                       JSON_OBJECT('sku', 'GP-02', 'qty', 10, 'unit_price', 15.5)))
FROM customers c WHERE c.id = 1;

UPDATE customers SET city = 'Mumbai' WHERE id = 1;

SELECT id, total, snapshot->>'$.customer.city' AS city_when_ordered, (SELECT city FROM customers WHERE id = 1) AS city_now FROM sales_orders;

Output

OUTPUT

+----+--------+-------------------+----------+
| id | total  | city_when_ordered | city_now |
+----+--------+-------------------+----------+
|  1 | 315.00 | Pune              | Mumbai   |
+----+--------+-------------------+----------+
1 row in set

The order still says Pune, as it did at checkout, while the customer’s current city is Mumbai. The totals and the lines of the order are fixed historical facts. The columns you query (customer_id, total) stay as real columns.

Pattern 5: tags without a join table (small scale)

query.sql

SQL

CREATE TABLE articles (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(40) NOT NULL, tags JSON NOT NULL, KEY idx_tags ((CAST(tags AS CHAR(20) ARRAY))));
INSERT INTO articles (title, tags) VALUES ('MySQL basics', '["sql", "mysql", "beginner"]'), ('PHP and PDO', '["php", "mysql"]'), ('CSS grid', '["css"]');

SELECT title FROM articles WHERE 'mysql' MEMBER OF (tags) ORDER BY id;
SELECT t.tag, COUNT(*) AS articles FROM articles, JSON_TABLE(tags, '$[*]' COLUMNS (tag VARCHAR(20) PATH '$')) AS t GROUP BY t.tag ORDER BY articles DESC, t.tag;

Output

OUTPUT

+--------------+
| title        |
+--------------+
| MySQL basics |
| PHP and PDO  |
+--------------+
2 rows in set

+----------+----------+
| tag      | articles |
+----------+----------+
| mysql    |        2 |
| beginner |        1 |
| css      |        1 |
| php      |        1 |
| sql      |        1 |
+----------+----------+
5 rows in set

A multi-valued index makes the “has this tag” search fast, and JSON_TABLE gives the tag counts. For a site where tags have their own pages, descriptions and rules, a proper tags table with a junction table is still the better model (Many-to-Many).

Habits that keep JSON healthy

Validate the stable parts with a JSON Schema CHECK (Validation).
Name keys consistently (snake_case or camelCase, never both).
Version your documents ("schema_version": 2), so that code can handle old shapes.
Do not put secrets in JSON that you log.
Keep documents small (kilobytes, not megabytes).
Extract the keys you query into indexed generated columns.

lightbulb

Start boring

Use JSON for the parts of your data that are really variable, and keep everything else in plain columns. That hybrid is easy to query, index and evolve.