JSON in SQL

Introduction

What is it? JSON (JavaScript Object Notation) is a standard, language-independent format for storing and exchanging semi-structured data. It is lightweight, human-readable, and used everywhere: APIs, apps, and databases.

Modern SQL databases (MySQL included) can store and query JSON directly. This gives a hybrid model:

• the reliability and structure of relational tables, and
• the flexibility of NoSQL-style data.

In simple terms, JSON is schema-flexible but syntax-strict:

• You do not have to define a fixed set of columns for every JSON document.
• But the JSON text itself must always follow valid JSON formatting rules.

Why do we need it? Some data fits neatly into fixed columns (id, email, created time). Other data changes from record to record: the items in an order, the device details, optional coupons, user preferences. Creating a column for every possible attribute is painful. JSON lets you keep the stable data in normal columns, and the variable data in one JSON column.

Real-world analogy Think of a shipping box with a printed label and a bag inside. The label has fixed fields (name, address, order number): those are the normal columns. The bag can hold different things in every box: a charger, a manual, a coupon. The bag is the JSON column. A path like $.items[0].sku is the instruction "open the bag, take the first item, read its code".

Business use case An online store keeps order_id, user_id, status, and total_amount as normal columns, because they are used in filters, joins, and reports. It keeps the full order payload (items, device, coupon, tax, duration) as JSON, because every order can look different and the payload arrives from outside systems.

 Relational + JSON in one row

 +----------+---------+--------+--------------+------------------------------------+
 | order_id | user_id | status | total_amount | order_details (JSON)               |
 +----------+---------+--------+--------------+------------------------------------+
 | 1        | 1       | PAID   | 4999.00      | {"items":[...], "device":{...},    |
 |          |         |        |              |  "coupon":{...}, "duration_sec":90}|
 +----------+---------+--------+--------------+------------------------------------+
   fixed, indexed, easy to query                flexible, varies per row

JSON Syntax Rules

To be valid, JSON must follow a few strict rules:

• Keys must always be written in double quotes.
• String values must also be written in double quotes.
• Commas, braces {}, and brackets [] must be placed correctly.
• Even though JSON does not need a pre-defined structure, it must still follow proper JSON syntax.

Example of valid JSON

{
  "student_name": "Asha",
  "age": 21,
  "skills": ["SQL", "Python", "DBMS"],
  "is_active": true
}

The JSON value types

Common ways JSON becomes invalid

The main idea:

• Relational tables give structure and consistency.
• JSON gives flexibility for variable or semi-structured information.

Dataset Used

Two tables are used in every example: users and orders.

-- Users Table
CREATE TABLE users (
    user_id        BIGINT PRIMARY KEY,
    email          VARCHAR(255) NOT NULL UNIQUE,
    created_at     DATETIME NOT NULL,
    user_profile   JSON NULL
);

-- Orders Table
CREATE TABLE orders (
    order_id           BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_order_id  VARCHAR(64) NOT NULL,  -- UNIQUE id from external systems (idempotency key)
    user_id            BIGINT NOT NULL,
    order_time         DATETIME NOT NULL,
    status             VARCHAR(20) NOT NULL,
    total_amount       DECIMAL(10,2) NOT NULL,
    order_details      JSON NULL,      -- semi-structured payload we will query a lot
    raw_payload_text   LONGTEXT NULL,  -- staging-like raw payload (can be invalid JSON)
    UNIQUE KEY uq_external_order_id (external_order_id),
    INDEX idx_orders_user_time (user_id, order_time),
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

Two JSON-related columns are worth noticing:

A JSON column rejects invalid JSON. A text column accepts anything. That is why the second column is used as a "raw landing area".

Sample rows: users

INSERT INTO users (user_id, email, created_at, user_profile)
VALUES
(
    1, 'asha@tuf.com', '2026-02-01 10:00:00',
    '{
        "name": "Asha",
        "age": "29",
        "address": {"city": "Bengaluru", "pincode": "560103"},
        "interests": ["dsa", "sql", "de"],
        "is_pro": true
    }'
),
(
    2, 'ravi@tuf.com', '2026-02-02 11:00:00',
    '{
        "name": "Ravi",
        "age": 31,
        "address": {"city": "Delhi"},
        "interests": ["ml", "sql"],
        "is_pro": false
    }'
),
(
    3, 'neha@tuf.com', '2026-02-03 09:30:00',
    '{
        "name": "Neha",
        "address": {"city": "Mumbai"},
        "interests": [],
        "is_pro": null
    }'
);

The three users are deliberately messy. Asha's age is text, Ravi's is a number, and Neha has no age at all. This is normal for JSON, and it is the reason you must be careful with types.

Sample rows: orders

INSERT INTO orders (
    external_order_id, user_id, order_time, status, total_amount,
    order_details, raw_payload_text
)
VALUES
(
    'ext_1001', 1, '2026-02-20 10:06:00', 'PAID', 4999.00,
    '{"items":[{"sku":"SKU123","qty":1,"price":4999}],
      "device":{"type":"mobile","os":"android"},
      "coupon":{"code":"FAIZAN14","discount_pct":14},
      "tax":{"gst_pct":18},
      "duration_sec":90}',
    '{"external_order_id":"ext_1001","user_id":1,"status":"PAID"}'
),
(
    'ext_1002', 2, '2026-02-20 11:10:00', 'FAILED', 2999.00,
    '{"items":[{"sku":"SKU999","qty":1,"price":2999}],
      "device":{"type":"desktop","os":"mac"},
      "coupon":{"code":null},
      "duration_sec":"90"}',
    '{"external_order_id":"ext_1002","user_id":2,"status":"FAILED"}'
),
(
    'ext_1003', 2, '2026-02-21 09:10:00', 'PAID', 2999.00,
    '{"items":[{"sku":"SKU123","qty":1,"price":2999},{"sku":"SKU777","qty":2,"price":100}],
      "device":{"type":"desktop"},
      "tax":{"gst_pct":18},
      "duration_sec":45}',
    '{"external_order_id":"ext_1003","user_id":2,"status":"PAID"}'
),
(
    'ext_1001_dup_payload', 1, '2026-02-20 10:06:30', 'PAID', 4999.00,
    '{"items":[{"sku":"SKU123","qty":1,"price":4999}],
      "device":{"type":"mobile"},
      "coupon":{"code":"FAIZAN14","discount_pct":14},
      "tax":{"gst_pct":18},
      "duration_sec":90}',
    '{"external_order_id":"ext_1001_dup_payload","user_id":1,"status":"PAID"}'
),
(
    'ext_bad_1', 3, '2026-02-22 08:00:00', 'CREATED', 0.00,
    '{"items":[],"device":{"type":"mobile"},"duration_sec":30}',
    '{ "external_order_id": "ext_bad_1", "user_id": 3, "status": "CREATED" '
);

A note on the data state. Later sections change or add rows (for example, ext_escape_demo is added and ext_1003 is updated). Each section says which state of the table its output comes from. Keyword names in this page are case-insensitive, but JSON keys are case-sensitive.

Syntax

Mental Model

A JSON document is a tree. A path is the route from the root ($) to the value you want.

 $                                   (the whole document)
 |-- name        "Asha"
 |-- age         "29"
 |-- address                         $.address
 |     |-- city      "Bengaluru"     $.address.city
 |     +-- pincode   "560103"
 |-- interests                       $.interests
 |     |-- [0]   "dsa"               $.interests[0]
 |     |-- [1]   "sql"
 |     +-- [2]   "de"
 +-- is_pro      true

Path syntax

How SQL Executes It

• When you insert into a JSON column, MySQL checks the JSON syntax, and stores it in an internal binary form, not as raw text.
• The stored object's keys may be re-ordered and extra spaces are removed. JSON object key order is not guaranteed, so do not depend on it.
• When you use a path in a query, MySQL walks the document for every row to find the value. That extra work is the source of the performance issues described later.
• JSON_EXTRACT() and -> return a JSON value (strings keep their quotes). ->> and JSON_UNQUOTE() return plain SQL text.
• A path that does not exist returns SQL NULL. It is not an error.

 JSON column  --(path)-->  JSON value       --(unquote)-->  SQL text
 order_details  $.device.type   "mobile"      ->> or JSON_UNQUOTE   mobile

1. Inserting JSON Data

There are two common ways to insert a value into a JSON column.

Pattern 1: A raw JSON literal

The simplest approach is to give the JSON document directly as a string.

INSERT INTO users (user_id, email, created_at, user_profile)
VALUES (999, 'demo@tuf.com', NOW(), '{"name":"Demo","age":30}');

The JSON value {"name":"Demo","age":30} is passed as a string. MySQL automatically validates the JSON syntax and stores it as JSON.

Pattern 2: Using CAST(... AS JSON)

INSERT INTO users (user_id, email, created_at, user_profile)
VALUES (998, 'demo2@tuf.com', NOW(), CAST('{"name":"Demo2","age":"31"}' AS JSON));

Here the string is explicitly converted into JSON before storing. Both ways give the same result. CAST is sometimes used for clarity or type safety, especially when JSON is built dynamically.

If the JSON is invalid, the insert fails with an error. For example, '{"name": "Demo"' (missing brace) is rejected.

Clean up the two demo rows so the outputs in the rest of this page match (they have nothing to do with the later examples):

DELETE FROM users WHERE user_id IN (998, 999);

2. JSON_VALID()

JSON_VALID() checks whether a value is a valid JSON document, in terms of syntax.

It returns:

• 1 if the JSON syntax is valid
• 0 if the JSON syntax is invalid

It only verifies JSON syntax rules. It does not check your business rules:

• It can tell whether commas, braces, brackets, and quotes are placed correctly.
• It cannot tell whether "duration_sec" should always be numeric.
• It cannot tell whether a required key like "coupon" is missing.

SQL Query

SELECT external_order_id,
       JSON_VALID(raw_payload_text) AS raw_is_valid
FROM orders
ORDER BY order_id;

Output

Explanation

• raw_payload_text stores the incoming payload as text.
• JSON_VALID(raw_payload_text) checks whether that text is valid JSON.
• The alias raw_is_valid makes the output readable.
• ORDER BY order_id keeps the output in table order.
• All rows except ext_bad_1 contain valid JSON. Its text is missing the closing }.

Find the bad rows before loading them into a JSON column

SELECT external_order_id, raw_payload_text
FROM orders
WHERE JSON_VALID(raw_payload_text) = 0;

Returns only ext_bad_1. This "land as text, validate, then load" pattern is common for data coming from outside systems.

3. JSON_EXTRACT()

JSON_EXTRACT(json_doc, path) reads a value from a JSON document using a path.

JSON_EXTRACT(json_doc, path)

• json_doc is the JSON column.
• path tells MySQL where the value sits inside the document (see the path table in "Mental Model").

Very important behavior

JSON_EXTRACT() returns a JSON value. Because of that:

• strings come back as quoted JSON strings
• numbers come back as numbers
• booleans come back as booleans
• JSON null comes back as JSON null

This matters a lot when you use string functions like CHAR_LENGTH() on the result.

Example 1: Extracting a string

SQL Query

SELECT user_id,
       JSON_EXTRACT(user_profile, '$.name') AS name_json,
       CHAR_LENGTH(JSON_EXTRACT(user_profile, '$.name')) AS length
FROM users;

Output

Explanation Names like Asha, Ravi, and Neha look like 4-character values, but the length is 6. JSON_EXTRACT() returns the string as a JSON string, which includes the double quotes: "Asha" has 4 letters + 2 quotes = 6. Some SQL editors hide the quotes when displaying the value, but the value is still quoted JSON text.

Example 2: Extracting a boolean

SQL Query

SELECT user_id,
       JSON_EXTRACT(user_profile, '$.is_pro') AS is_pro_json,
       CHAR_LENGTH(JSON_EXTRACT(user_profile, '$.is_pro')) AS length
FROM users;

Output

Explanation

• true has length 4 and false has length 5. Booleans are not wrapped in quotes.
• For user 3, is_pro is a JSON null. It is a real JSON value, and as text it is the four letters null. That is why the length is 4.
• Some tools draw this value as NULL, which can be confusing. It is not the same as SQL NULL. Section 7 explains the difference.

Example 3: Extracting a number (and a number-like string)

SQL Query

SELECT user_id,
       JSON_EXTRACT(user_profile, '$.age') AS age_json,
       CHAR_LENGTH(JSON_EXTRACT(user_profile, '$.age')) AS length
FROM users;

Output

Explanation

• User 2's age is stored as a JSON number, so it comes back as 31 with no quotes: length 2.
• User 1's age is stored as a JSON string ("29"), so it comes back with quotes: length 4. The same age, written two ways, behaves differently.
• User 3 has no age key at all. A missing path returns SQL NULL, so the length is also NULL.

Why it works JSON keeps the type of every value (string, number, boolean, null). That is flexible, but it means "29" and 29 are different values.

4. The -> Operator

MySQL provides a shorthand for extraction:

json_column -> '$.path'

It behaves exactly like JSON_EXTRACT(json_column, '$.path'), so it also returns a JSON value.

SQL Query

SELECT user_id,
       user_profile -> '$.address.city' AS city_json
FROM users;

Output

Explanation The path $.address.city means:

• $ is the entire document
• .address moves into the address object
• .city picks the city key inside address

Since -> is shorthand for JSON_EXTRACT(), the result is still a JSON value (the quotes are part of it).

5. The ->> Operator

The ->> operator extracts a value and returns it as unquoted plain SQL text.

json_column ->> '$.path'

It is extremely useful when you want to:

• get plain SQL text instead of quoted JSON text
• compare values in a WHERE clause
• use string functions without the JSON quotes getting in the way

It is the same as JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.path')).

SQL Query

SELECT user_id,
       user_profile ->> '$.address.city' AS city_text
FROM users;

Output

The output looks similar to the last query, but the type is different: -> returns JSON, ->> returns unquoted SQL text.

Comparison: -> vs ->>

SQL Query

SELECT
    user_id,
    user_profile -> '$.address.city' AS city1,
    CHAR_LENGTH(user_profile -> '$.address.city') AS len1,
    user_profile ->> '$.address.city' AS city2,
    CHAR_LENGTH(user_profile ->> '$.address.city') AS len2
FROM users;

Output

Explanation, row 1

• city1 comes from ->, so internally it is "Bengaluru": 9 letters + 2 quotes = length 11.
• city2 comes from ->>, so it is plain text Bengaluru: length 9.
• Same idea for Delhi (7 vs 5) and Mumbai (8 vs 6).

 -> returns JSON text          ->> returns plain text

   "Bengaluru"   (11 chars)       Bengaluru   (9 chars)

When you want a plain text value for filtering, comparison, or string functions, ->> is usually more convenient.

6. JSON_UNQUOTE()

JSON_UNQUOTE() removes the quotes from a quoted JSON string and turns it into a normal SQL string.

JSON_UNQUOTE(json_value)

It is useful when the input comes from JSON_EXTRACT(...) or column -> '$.path', because both return JSON values, in which strings stay quoted.

SQL Query

SELECT user_id,
       JSON_UNQUOTE(JSON_EXTRACT(user_profile, '$.name')) AS name_text,
       CHAR_LENGTH(JSON_EXTRACT(user_profile, '$.name')) AS len_extraction,
       CHAR_LENGTH(JSON_UNQUOTE(JSON_EXTRACT(user_profile, '$.name'))) AS len
FROM users;

Output

Explanation, step by step

1. JSON_EXTRACT(user_profile, '$.name') returns "Asha".
2. The length of "Asha" is 6.
3. JSON_UNQUOTE(...) converts "Asha" into Asha.
4. The length of Asha is 4.

->> vs JSON_UNQUOTE JSON_UNQUOTE(JSON_EXTRACT(user_profile, '$.name')) and user_profile ->> '$.name' give the same result. The difference is in how they are used: ->> always needs a JSON path, while JSON_UNQUOTE() can also be used directly on any quoted JSON string value.

7. JSON null, SQL NULL, and a Missing Key

This is one of the most important ideas in this chapter. A JSON value can be "nothing" in four different ways:

The surprising row is the second one: ->> turns a JSON null into the four-letter text null, not into SQL NULL.

 Is the key there?
    |
    +-- no  ---------------------------------> SQL NULL        (->> and ->)
    |
    +-- yes, value is JSON null ->  -> gives JSON null,  ->> gives the TEXT 'null'
    +-- yes, value is ""        ->  empty string
    +-- yes, real value         ->  the value

COALESCE() with JSON data

COALESCE(expr1, expr2, ...) returns the first non-NULL value. It is handy when a JSON key may be missing and you want a fallback.

SQL Query

SELECT external_order_id,
       COALESCE(order_details ->> '$.coupon.code', 'NO_COUPON') AS coupon_code
FROM orders
ORDER BY order_id;

Output

Explanation

• ext_1003 and ext_bad_1 have no coupon key at all. The extraction returns SQL NULL, so COALESCE uses the fallback NO_COUPON.
• ext_1002 has "code": null. The extraction returns the text null, which is not SQL NULL, so COALESCE keeps it. You see the word null, not NO_COUPON.

The fix: turn the text 'null' into SQL NULL first

SELECT external_order_id,
       COALESCE(NULLIF(order_details ->> '$.coupon.code', 'null'), 'NO_COUPON') AS coupon_code
FROM orders
ORDER BY order_id;

NULLIF(x, 'null') returns NULL when x equals the text null. Now ext_1002 also becomes NO_COUPON.

Practical lesson. Always separate these cases:

• key not present
• key present but value is JSON null
• key present with an empty string
• key present with an actual value

They behave differently in production pipelines.

8. CAST()

CAST() explicitly converts a value from one type to another.

CAST(value AS UNSIGNED)

It is commonly used when a value is extracted from JSON as text, but you want to treat it as a number in SQL (for filtering, sorting, aggregation, or reports).

A very common production situation: JSON stores a numeric-looking value as text.

Setup for the next two examples. Make one order hold a bad value for duration_sec (the JSON_SET function is explained in section 15):

UPDATE orders
SET order_details = JSON_SET(order_details, '$.duration_sec', 'abc')
WHERE external_order_id = 'ext_1001';

SQL Query

SELECT external_order_id,
       order_details ->> '$.duration_sec' AS duration_raw,
       CAST(order_details ->> '$.duration_sec' AS UNSIGNED) AS duration_cast
FROM orders;

Output

Explanation

• Valid numeric text converts normally: '90' becomes 90, '45' becomes 45, '30' becomes 30.
• For 'abc', MySQL converts it to 0 (with a warning) when cast as UNSIGNED.

Why this is dangerous. That 0 may not be real business data. If abc silently becomes 0:

• the average duration becomes wrong
• duration-based filters become wrong
• reports include bad records as "zero-duration" orders

9. Safe Casting with CASE + REGEXP

Safe casting means: "Cast only when the value looks valid. Otherwise return NULL." It avoids silently turning bad data into a meaningless 0.

SQL Query

SELECT external_order_id,
       order_details ->> '$.duration_sec' AS duration_raw,
       CASE
           WHEN (order_details ->> '$.duration_sec') REGEXP '^[0-9]+$'
               THEN CAST(order_details ->> '$.duration_sec' AS UNSIGNED)
           ELSE NULL
       END AS duration_safe
FROM orders;

The regex '^[0-9]+$'

So the whole string must contain only digits.

Output

Explanation

1. The query extracts the raw text duration.
2. It checks whether the text contains only digits.
3. It casts only if the regex check passes.

abc does not match, so the result is NULL. Valid numeric strings are converted.

Why NULL is better than 0 NULL signals "invalid or unusable data". 0 looks like real data and can silently corrupt reports.

Notes

• This pattern rejects negative numbers and decimals (-5, 12.5). To allow a minus sign, use '^-?[0-9]+$'.
• If the value is missing (SQL NULL), the REGEXP test is NULL, the WHEN is not true, and the result is NULL.

Production recommendation. When you extract numbers from JSON text, do validation first, casting second. That gives much safer analytics and fewer hidden bugs.

10. The First Look at JSON Performance

Using a JSON path inside WHERE, GROUP BY, or similar clauses can create performance problems on large tables. MySQL often has to:

1. read each row
2. extract the JSON path
3. compare the result
4. repeat for the next row

Without a suitable expression, generated-column, or multi-valued index, this commonly leads to a scan.

SQL Query

SELECT COUNT(*)
FROM orders
WHERE order_details ->> '$.coupon.code' = 'FAIZAN14';

Output

Explanation This counts the orders with coupon code FAIZAN14 (ext_1001 and ext_1001_dup_payload). It is correct, but correctness is not the only concern. On large tables, performance matters too.

Look at the plan with EXPLAIN

EXPLAIN FORMAT=TRADITIONAL
SELECT order_id
FROM orders
WHERE order_details ->> '$.coupon.code' = 'FAIZAN14';

How to read it

• type = ALL means a full table scan.
• key = NULL means no index was used.
• MySQL scans every row, extracts the coupon code from each one, and then checks whether it equals 'FAIZAN14'.

With 5 rows this is fine. With millions of rows, it becomes expensive.

Why indexing is difficult here. When data is buried inside JSON, MySQL must extract the value first and only then compare it. That extraction can happen for each row. So although JSON is flexible, repeated filtering and grouping on JSON paths can become slow at scale. Section 17 shows the fix (generated columns).

11. Array Extraction

Array extraction means reading a value from a JSON array using its index. In MySQL JSON paths, arrays are zero-indexed:

• first element: [0]
• second element: [1]
• third element: [2]

If an array has n elements, valid indexes go from 0 to n - 1.

 "items": [  { sku: SKU123 }  ,  { sku: SKU777 }  ]
               index [0]           index [1]

SQL Query

SELECT external_order_id,
       order_details ->> '$.items[0].sku' AS first_sku,
       order_details ->> '$.items[0].qty' AS first_qty
FROM orders;

Output

Explanation

• $.items goes to the items array.
• [0] picks the first object inside the array.
• .sku and .qty read fields from that first object.
• If the array is empty (ext_bad_1), there is no first element, and the result is NULL.

The second element

SELECT external_order_id,
       order_details ->> '$.items[1].sku' AS second_sku,
       order_details ->> '$.items[1].qty' AS second_qty
FROM orders
WHERE external_order_id = 'ext_1003';

The index is [1], so this reads the second element.

More path tricks (extra)

12. JSON_SEARCH()

JSON_SEARCH() searches for a string value inside a JSON document and returns the path where it was found.

JSON_SEARCH(json_doc, one_or_all, search_str, escape_char, path...)

Two facts to remember:

• JSON_SEARCH only finds string values. It cannot find a number like 90.
• It returns a path, not the value. If nothing matches, it returns NULL.

'one' returns one path

SQL Query

SELECT external_order_id,
       JSON_SEARCH(order_details, 'one', 'SKU123', NULL, '$.items[*].sku') AS found_path
FROM orders;

Output

If the value is found, MySQL returns the matching path. If not, NULL.

'all' returns every matching path

SQL Query

SELECT external_order_id,
       JSON_SEARCH(order_details, 'all', 'SKU123', NULL, '$.items[*].sku') AS found_path
FROM orders;

Output

Explanation With 'all', MySQL returns every matching path. If there is exactly one match, the result is just the path string (as above). If there are two or more, the result is a JSON array of paths (you will see this below). Here every order has at most one SKU123 item, so all results are plain strings.

Searching several paths

You can pass more than one path. To make this example interesting, first store the text SKU123 in ext_1001's duration_sec (this replaces the abc test value from section 8):

UPDATE orders
SET order_details = JSON_SET(order_details, '$.duration_sec', 'SKU123')
WHERE external_order_id = 'ext_1001';

Now ext_1001 contains SKU123 in two places: $.items[0].sku and $.duration_sec.

Query 1: items first, then duration_sec

SELECT external_order_id,
       JSON_SEARCH(order_details, 'one', 'SKU123', NULL, '$.items[*].sku', '$.duration_sec') AS found_path
FROM orders;

Query 2: duration_sec first, then items

SELECT external_order_id,
       JSON_SEARCH(order_details, 'one', 'SKU123', NULL, '$.duration_sec', '$.items[*].sku') AS found_path
FROM orders;

Changing the order of the paths changed the answer for ext_1001. In practice MySQL often follows the order you give. But the MySQL manual says which match counts as "first" is not guaranteed, so do not build important logic on it.

Query 3: 'all' with both paths

SELECT external_order_id,
       JSON_SEARCH(order_details, 'all', 'SKU123', NULL, '$.duration_sec', '$.items[*].sku') AS found_path
FROM orders;

ext_1001 has two matches, so the result is an array. The other rows have one match (a plain string) or none (NULL).

Wildcards in the search string

The search string supports wildcards, like the LIKE operator:

• % matches any number of characters (including none)
• _ matches exactly one character

Demo insert (a row with tricky SKU values):

INSERT INTO orders (external_order_id, user_id, order_time, status, total_amount, order_details)
VALUES (
  'ext_escape_demo', 1, NOW(), 'PAID', 100.00,
  '{
    "items": [
      {"sku": "SKU90123", "qty": 1, "price": 10},
      {"sku": "SKU%123",  "qty": 1, "price": 20},
      {"sku": "SKU_123",  "qty": 1, "price": 30}
    ],
    "device": {"type":"desktop"}
  }'
);

The three SKUs are SKU90123 (two characters between SKU and 123), SKU%123 (a real percent sign), and SKU_123 (a real underscore).

Wildcard %

SELECT external_order_id,
       JSON_SEARCH(order_details, 'all', 'SKU%123', NULL, '$.items[*].sku') AS paths
FROM orders
WHERE external_order_id = 'ext_escape_demo';

Because % is a wildcard, it matches all three values.

Wildcard _

SELECT external_order_id,
       JSON_SEARCH(order_details, 'all', 'SKU_123', NULL, '$.items[*].sku') AS path_wildcard_underscore
FROM orders
WHERE external_order_id = 'ext_escape_demo';

_ is a one-character wildcard. It matches SKU%123 (the % is one character) and SKU_123, but not SKU90123 (two characters between SKU and 123).

The escape character

To search for a real % or _, put an escape character in front of it, and tell MySQL which character is the escape character (the 4th argument).

Literal %

SELECT external_order_id,
       JSON_SEARCH(order_details, 'all', 'SKU$%123', '$', '$.items[*].sku') AS path_literal_percent
FROM orders
WHERE external_order_id = 'ext_escape_demo';

Here $ is the escape character, so $% means "a real percent sign".

Literal _

SELECT external_order_id,
       JSON_SEARCH(order_details, 'all', 'SKU@_123', '@', '$.items[*].sku') AS path_only_underscore
FROM orders
WHERE external_order_id = 'ext_escape_demo';

Here @ is the escape character, so @_ means "a real underscore".

13. JSON_CONTAINS()

JSON_CONTAINS() checks whether a JSON document contains a given candidate value or object.

JSON_CONTAINS(target, candidate [, path])

It returns 1 if the target contains the candidate, 0 if not, and NULL if any argument is NULL.

SQL Query

SELECT external_order_id
FROM orders
WHERE JSON_CONTAINS(order_details, '{"sku":"SKU123","qty":1,"price":4999}', '$.items');

Output

Explanation

• The third argument '$.items' says "look inside the items array".
• The candidate is an object. An array contains it if one of its elements has all the same key-value pairs.
• ext_1003 also has an SKU123 item, but its price is 2999, not 4999, so it does not match.

This is useful when you want to check the existence of an object inside an array.

Rules for what "contains" means

14. Helper Functions (extra)

These small functions make JSON easier to inspect. The results use the original data.

JSON_TYPE()

SELECT user_id,
       JSON_TYPE(user_profile -> '$.age')    AS age_type,
       JSON_TYPE(user_profile -> '$.is_pro') AS is_pro_type
FROM users;

JSON_TYPE shows the difference between a missing key (SQL NULL result) and a JSON null (the type name NULL). It also reveals the mixed age types (STRING vs INTEGER).

JSON_LENGTH() and JSON_KEYS()

SELECT user_id,
       JSON_LENGTH(user_profile -> '$.interests') AS interest_count,
       JSON_KEYS(user_profile -> '$.address')     AS address_keys
FROM users;

JSON_CONTAINS_PATH()

Tells whether a path exists, even if its value is null.

SELECT external_order_id,
       JSON_CONTAINS_PATH(order_details, 'one', '$.coupon.code') AS has_coupon_key,
       JSON_TYPE(order_details -> '$.coupon.code')               AS coupon_type
FROM orders
ORDER BY order_id;

(Values are from the original data, before the update examples.)

15. Updating JSON

Until now we read JSON. These functions change it. Each one returns a new JSON document, so you use them in UPDATE ... SET column = JSON_xxx(column, ...).

JSON_SET()

JSON_SET() inserts or updates values inside a JSON document.

• If the path already exists, it updates the value.
• If the path does not exist, it creates the key and inserts the value.

So it behaves like "insert if missing, update if present".

JSON_SET(json_doc, path1, value1, path2, value2, ...)

You can give several path-value pairs, so one query can change several places.

Example 1: Insert a nested key directly (it does not work)

UPDATE orders
SET order_details = JSON_SET(order_details, '$.coupon.code', 'NEW10')
WHERE external_order_id = 'ext_1003';

You would expect:

"coupon": { "code": "NEW10" }

But nothing happens. ext_1003 has no coupon key, and MySQL cannot create a nested child path when the parent object is missing. There is no error, the row is simply unchanged.

The most important rule of JSON_SET()

• The parent path must exist. MySQL can create $.coupon, but it cannot directly create $.coupon.code if coupon itself is missing.
• Parent exists => the child can be inserted.
• Parent missing => the nested insert fails silently.

Example 2: First create the parent object

UPDATE orders
SET order_details = JSON_SET(order_details, '$.coupon', JSON_OBJECT())
WHERE external_order_id = 'ext_1003';

Result: "coupon": {}. JSON_OBJECT() creates an empty JSON object, so coupon now exists.

Example 3: Now insert inside the coupon

UPDATE orders
SET order_details = JSON_SET(order_details, '$.coupon.code', 'NEW10')
WHERE external_order_id = 'ext_1003';

Result: "coupon": { "code": "NEW10" }. The parent exists, so the child insert succeeds.

Example 4: Update an existing value

UPDATE orders
SET order_details = JSON_SET(order_details, '$.duration_sec', '100')
WHERE external_order_id = 'ext_1003';

Result: "duration_sec": "100". The key already exists, so it is updated.

A type warning: '100' (with quotes) is a string. The old value 45 was a number, so the JSON type of the key changed from number to string. If you want to keep it a number, write 100 without quotes.

JSON_REPLACE()

JSON_REPLACE() only updates existing paths. It never creates new keys. If the path does not exist, nothing happens.

JSON_REPLACE(json_doc, path1, value1, path2, value2, ...)

Example: Replace a key that does not exist

UPDATE orders
SET order_details = JSON_REPLACE(order_details, '$.coupon.max_uses', 100)
WHERE external_order_id = 'ext_1003';

$.coupon exists ({"code":"NEW10"}), but max_uses does not. Nothing happens. If the key did exist, the replacement would succeed.

To create the key, use JSON_SET (the parent coupon exists, so this works):

UPDATE orders
SET order_details = JSON_SET(order_details, '$.coupon.max_uses', 100)
WHERE external_order_id = 'ext_1003';

Now coupon is {"code":"NEW10","max_uses":100}.

JSON_SET vs JSON_INSERT vs JSON_REPLACE

All three write values. They differ in what they do when the path exists or not.

SELECT
  JSON_SET    ('{"a":1,"b":2}', '$.a', 10, '$.c', 99) AS set_result,
  JSON_INSERT ('{"a":1,"b":2}', '$.a', 10, '$.c', 99) AS insert_result,
  JSON_REPLACE('{"a":1,"b":2}', '$.a', 10, '$.c', 99) AS replace_result;

JSON_REMOVE()

JSON_REMOVE() deletes keys or paths from a JSON document. It removes exactly the path you specify. If the path is missing, nothing happens.

JSON_REMOVE(json_doc, path1, path2, ...)

Example

UPDATE orders
SET order_details = JSON_REMOVE(order_details, '$.coupon')
WHERE external_order_id = 'ext_1003';

Before:

"coupon": { "code": "NEW10", "max_uses": 100 }

After: coupon is removed completely. Because coupon is removed at the top level, all its nested keys disappear too. Removing a parent removes all its children automatically.

Removing an array element (extra)

SELECT JSON_REMOVE('{"a":1,"b":[10,20,30]}', '$.b[1]') AS result;

JSON_ARRAY_APPEND()

JSON_ARRAY_APPEND() adds a new value at the end of an existing JSON array. It is used when arrays need to grow.

Typical production uses: add a new order item, append an event log entry, add a tag, add a metadata object.

JSON_ARRAY_APPEND(json_doc, path, value)

Example

UPDATE orders
SET order_details = JSON_ARRAY_APPEND(
    order_details,
    '$.items',
    CAST('{"sku":"SKU555","qty":1,"price":50}' AS JSON)
)
WHERE external_order_id = 'ext_1001';

Why CAST? The object must be inserted as JSON, not as plain text. Without CAST, MySQL stores the text as a JSON string (a quoted piece of text) inside the array, not as an object. JSON_OBJECT('sku','SKU555','qty',1,'price',50) works too.

After this update, ext_1001 has two items: the original SKU123 and the new SKU555.

Other array helpers (extra)

16. JSON Output Builders

So far we stored, read, searched, and updated JSON. Now comes a practical question: can SQL itself build JSON output for us? Yes.

In real applications, the backend often returns API responses in JSON. A common approach is:

1. run a SQL query
2. fetch tabular rows into the application
3. stitch those rows into JSON in backend code
4. return the JSON to the client

This works, but it adds extra logic in the application layer. JSON output builders move some of that work into SQL. SQL can directly produce:

• JSON objects
• JSON arrays
• JSON maps / dictionaries

 rows  --(JSON_OBJECT)-->  one JSON object per row
 rows  --(JSON_ARRAYAGG)-->  one JSON array for a group of rows
 rows  --(JSON_OBJECTAGG)-->  one JSON map (key -> value) for a group of rows

Real use cases

• API-ready JSON directly from SQL
• Compact export payloads
• Less application-side stitching
• Nested response structures
• Grouped JSON responses for parent-child data

They are best when the response is not too large and the JSON shape is well understood.

When not to use them. Avoid them for very large outputs, because then:

• the database spends more CPU building a heavy JSON payload
• the response becomes large
• database-to-application transfer becomes expensive
• your production database gets unnecessary pressure

For heavy analytics or huge exports, it is usually better to use a warehouse such as BigQuery or Snowflake, or a cheap object store such as S3 or GCS. Production databases should mainly serve real-time application work, not massive JSON exports.

Data state for the examples in this section. The table has the 5 original orders plus the ext_escape_demo row (order_id 6). The rows ext_1002 (status FAILED) and ext_1003 are unchanged in the columns used here.

JSON_OBJECT()

JSON_OBJECT() creates a JSON object from key-value pairs.

JSON_OBJECT(key1, value1, key2, value2, ...)

Rules

• Keys must be strings.
• Values can come from columns, expressions, JSON extraction, or constants.
• If a value is SQL NULL, the key is still included and its value becomes null. The key is not removed.

Example 1

SELECT user_id,
       JSON_OBJECT(
         'user_id', user_id,
         'email', email,
         'city', user_profile ->> '$.address.city',
         'age', user_profile ->> '$.age',
         'dummy', NULL
       ) AS user_json
FROM users;

Output

Explanation

• For every row in users, one JSON object is built.
• Even though we passed NULL, the key is still there: "dummy": null.
• For user 3, age is missing in the profile, so the extracted value is NULL and the key shows null.
• The displayed key order is an implementation detail. MySQL does not promise to preserve insertion order or return keys in any particular order.
• ->> returns text, so a numeric age becomes the string "31". To keep the original JSON type, use -> instead ('age', user_profile -> '$.age'), which gives 31 as a number for Ravi and "29" as a string for Asha.

Nested JSON with JSON_OBJECT()

JSON_OBJECT() can contain another JSON_OBJECT(), so you can build nested structures.

Example 2

SELECT user_id,
       JSON_OBJECT(
         'user_id', user_id,
         'email', email,
         'city', user_profile ->> '$.address.city',
         'age', user_profile ->> '$.age',
         'dummy', JSON_OBJECT(
             'dummy1', NULL
         )
       ) AS user_json
FROM users;

Output

Here dummy is no longer a plain null. It is a nested object: "dummy": {"dummy1": null}.

JSON_ARRAYAGG()

JSON_ARRAYAGG() collects values from many rows into one JSON array.

• JSON_OBJECT() builds one object from one row.
• JSON_ARRAYAGG() gathers many row-level values into one array.

JSON_ARRAYAGG(expression)

The expression can be a scalar, a JSON object, a JSON extraction result, or another expression.

• If the expression is a JSON object, the result is an array of JSON objects.
• If the expression is a scalar, the result is an array of scalar values.

Example 1: Each user with an array of their orders

SELECT
  o.user_id,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'external_order_id', o.external_order_id,
      'time', DATE_FORMAT(o.order_time, '%Y-%m-%d %H:%i:%s'),
      'status', o.status,
      'amount', o.total_amount,
      'device', o.order_details ->> '$.device.type',
      'coupon', o.order_details ->> '$.coupon.code'
    )
  ) AS orders_json
FROM orders o
GROUP BY o.user_id
ORDER BY o.user_id;

Output

(The time of ext_escape_demo comes from NOW(), so yours will differ.)

Explanation

• Rows are grouped by user_id: all orders of user 1 become one group, and so on.
• For each row inside the group, JSON_OBJECT() builds one object.
• JSON_ARRAYAGG() puts all those objects into one array.
• The result is one row per user, with one array holding all their orders.

 For user 1, SQL builds something like:

 [
   { "external_order_id": "...", "status": "...", ... },
   { "external_order_id": "...", "status": "...", ... },
   { "external_order_id": "...", "status": "...", ... }
 ]

That is extremely useful in API responses, where a parent entity needs a child collection.

Look closely at user 2, the first order. Its coupon shows "null" in quotes (a text), while other orders without a coupon show null (no quotes). This is the section 7 effect: ->> on a JSON null gives the text null, and a missing key gives SQL NULL, which becomes a real JSON null.

Example 2: An array of single values

SELECT
  external_order_id,
  JSON_ARRAYAGG(order_details ->> '$.items[0].sku') AS sku_array_demo
FROM orders
GROUP BY external_order_id;

Output

Explanation This time we are not building JSON objects. We aggregate one extracted scalar value, so the result is an array of scalars (["SKU123"]), not [{"sku":"SKU123"}]. The empty items array of ext_bad_1 gives [null].

Important note about order. MySQL does not guarantee the order of elements produced by JSON_ARRAYAGG(), and this syntax has no aggregate-local ORDER BY. A sorted subquery is not a reliable ordering guarantee because the optimizer may ignore or merge that ordering. If array order is part of the contract, use an approach whose final aggregation explicitly guarantees it, or order the values after retrieval. Do not infer order from a test result.

JSON_OBJECTAGG()

JSON_OBJECTAGG() aggregates rows into a JSON object (a map or dictionary). Instead of an array, it builds key-value pairs.

JSON_OBJECTAGG(key_expression, value_expression)

For each row it takes one key and one value and puts them into a single JSON object.

 JSON_ARRAYAGG gives:        JSON_OBJECTAGG gives:

 [ {...}, {...}, {...} ]     { "key1": value1, "key2": value2 }

Example: A map of status counts per user

SELECT
  x.user_id,
  JSON_OBJECTAGG(x.status, x.cnt) AS status_count_map
FROM (
  SELECT user_id, status, COUNT(*) AS cnt
  FROM orders
  GROUP BY user_id, status
) x
GROUP BY x.user_id
ORDER BY x.user_id;

Output

Step 1: The inner query counts how many orders each user has for each status:

Step 2: The outer query uses JSON_OBJECTAGG(status, cnt) for each user: the key is the status, and the value is the count. For user 2, that gives {"PAID": 1, "FAILED": 1}.

Why pre-aggregation is important. JSON objects need meaningful, unique keys. If duplicate keys appear unexpectedly, the behavior can become tricky. A NULL key is an error. The safest pattern is: first prepare clean grouped rows, then convert them into a JSON object. That is exactly why the subquery is used here.

17. Performance and Indexing: Generated Columns

Working with JSON feels powerful, because you can store flexible data without a rigid schema for every attribute. But as soon as you query JSON fields in WHERE, GROUP BY, or ORDER BY, performance becomes a serious concern.

Why JSON filtering becomes slow

SELECT order_id
FROM orders
WHERE order_details ->> '$.device.type' = 'desktop';

Without a suitable expression, generated-column, or multi-valued index, MySQL cannot jump straight to the rows where device.type = 'desktop'. It commonly ends up doing:

 read one row
 extract the JSON value from that row
 compare it
 move to the next row
 extract again
 compare again
 ... repeat for ALL rows

This is row-by-row extraction. With 5 or 6 rows you will not feel it. With 10 lakh, 5 crore, or 100 crore rows, it becomes painful.

Definition. A JSON performance issue happens when MySQL is forced to repeatedly extract values from JSON documents at query time, instead of using an index-friendly, precomputed column. MySQL is very good at indexing normal columns. But if your filter depends on a JSON path directly, it usually has to compute that value again and again for each row.

The query and its result (state: 5 original orders + ext_escape_demo):

The answer is correct. The real question is: how much work did MySQL do to get it? That is what EXPLAIN tells us.

Debugging with EXPLAIN

EXPLAIN FORMAT=TREE
SELECT order_id
FROM orders
WHERE order_details ->> '$.device.type' = 'desktop';

-> Filter: (json_unquote(json_extract(orders.order_details,'$.device.type')) = 'desktop')  (cost=... rows=...)
    -> Table scan on orders  (cost=... rows=...)

(The cost and row numbers are estimates and depend on your data.)

The important words are Table scan on orders. MySQL scans the whole table. It is not using an index to jump to the matching rows. It is saying: "Let me inspect every row one by one." That is the warning sign.

In production this hurts because:

• orders may have millions of rows
• every dashboard may run several filters
• business queries may group by JSON-derived fields
• analysts may repeatedly query the same JSON paths

So the rule is: do not depend on raw JSON paths for high-frequency filtering or grouping.

The solution: generated columns

A generated column is a column whose value is automatically computed from an expression, often from another column such as a JSON document.

• compute device_type from order_details ->> '$.device.type'
• compute coupon_code from order_details ->> '$.coupon.code'

Generated columns behave much like normal relational columns, and you can index them. Both STORED and indexed VIRTUAL columns add computation and index-maintenance work to writes.

Why it works. Generated columns move the work from query time to write time:

• when a row is inserted, the generated column is computed
• when the JSON is updated, the generated column is recomputed
• the computed value is stored on disk
• indexes work on that stored value

So reads become much faster. This is a bridge between JSON flexibility and relational performance.

 Before:  row => extract JSON => compare => next row
 After:   row already has a device_type column => use the index => jump to matching rows

Creating generated columns on orders

ALTER TABLE orders
  ADD COLUMN device_type VARCHAR(30)
    GENERATED ALWAYS AS (order_details ->> '$.device.type') STORED,
  ADD COLUMN coupon_code VARCHAR(30)
    GENERATED ALWAYS AS (order_details ->> '$.coupon.code') STORED,
  ADD INDEX idx_orders_device_type (device_type),
  ADD INDEX idx_orders_coupon_code (coupon_code);

This one statement does four things:

1. Adds device_type, generated from order_details ->> '$.device.type'. For JSON {"device": {"type": "desktop"}}, device_type becomes desktop.
2. Adds coupon_code, generated from order_details ->> '$.coupon.code'.
3. Marks both as STORED: the computed value is physically saved on disk, not recomputed on every read.
4. Adds indexes on both columns, so they can be filtered quickly.

After the ALTER TABLE, the new columns look like this for the 6 existing orders:

MySQL automatically read the existing JSON documents and filled the new columns, so old rows were populated too. Notice the second row: 'null' is the text null (the JSON null effect from section 7). A missing coupon is a real SQL NULL.

Now query the generated column instead of the JSON path

SELECT order_id
FROM orders
WHERE device_type = 'desktop';

This filters on a normal indexed column. On a large table, EXPLAIN would show that MySQL uses idx_orders_device_type (access type ref) instead of a full scan.

STORED vs VIRTUAL (extra)

Production warning. Adding a STORED generated column and indexes to a huge table rebuilds the table. That can take a long time and use a lot of resources. Plan it for a quiet period, or use an online schema-change tool. Also remember: the generated expression runs on every write, so a bad expression (for example, a CAST of a non-numeric string) can produce wrong values or errors, depending on the SQL mode.

The same idea on users

ALTER TABLE users
  ADD COLUMN city VARCHAR(50)
    GENERATED ALWAYS AS (user_profile ->> '$.address.city') STORED,
  ADD COLUMN age_int INT
    GENERATED ALWAYS AS (CAST(user_profile ->> '$.age' AS UNSIGNED)) STORED,
  ADD INDEX idx_users_city (city),
  ADD INDEX idx_users_age_int (age_int);

Result:

age_int fixes the mixed-type problem: Asha's text age "29" and Ravi's number 31 both become real integers that you can sort, filter, and index. Neha has no age, so her value stays NULL.

Insert after adding generated columns

INSERT INTO orders (external_order_id, user_id, order_time, status, total_amount, order_details)
VALUES ('ext_x', 1, NOW(), 'PAID', 100,
        '{"device":{"type":"desktop"}}');

You insert only order_details. You do not give device_type. MySQL computes it automatically:

So generated columns stay in sync with the JSON automatically. That is why they are reliable for production querying.

Other ways to index JSON (for later). MySQL 8 also offers functional indexes (8.0.13) and multi-valued indexes for JSON arrays (8.0.17). Generated columns are often the clearest MySQL approach, but support and syntax vary across database systems; they are not universally portable.

18. Upsert

What is upsert? Upsert means insert if the row does not exist, otherwise update the existing row. The word comes from UPdate + inSERT. Instead of handling duplicates manually in application code, you let SQL handle it.

Why does it matter? Imagine external_order_id is unique, so the same external order cannot be inserted twice. If you insert it again, MySQL normally throws a unique-constraint error. But in real applications that repeated insert may be completely valid, for example:

• the order already exists, but its status changed from FAILED to PAID
• its items changed
• a newer event came in later

In such cases, failing the insert is not helpful. You want the existing row to be updated.

Real-life mental model. Think of a shopping app. You add a few items to the cart, then go back and add more.

• Wrong approach: create a brand new order every time something changes. That creates duplicate order records and a mess.
• Correct approach: keep the same order identity and update the existing row. That is the upsert mindset.

 INSERT arrives
     |
     v
 Does a row with the same unique key exist?
     |                         |
    no                        yes
     |                         |
  insert a new row       update the existing row instead

18.1 Basic upsert with ON DUPLICATE KEY UPDATE

This is the most important upsert feature in MySQL. It means: try inserting first; if a duplicate-key conflict happens, update the existing row instead.

Example

INSERT INTO orders (external_order_id, user_id, order_time, status, total_amount, order_details, raw_payload_text)
VALUES (
    'ext_1002',
    2,
    '2026-02-20 11:10:00',
    'PAID',
    2999.00,
    '{"items":[{"sku":"SKU999","qty":1,"price":2999}],"device":{"type":"desktop"},"duration_sec":"90"}',
    '{"external_order_id":"ext_1002","user_id":2,"status":"PAID"}'
)
ON DUPLICATE KEY UPDATE
    status = VALUES(status),
    total_amount = VALUES(total_amount),
    order_details = VALUES(order_details),
    raw_payload_text = VALUES(raw_payload_text);

This query says:

1. Try inserting a new row for ext_1002.
2. If no duplicate exists, insert it normally.
3. But if external_order_id = 'ext_1002' already exists, then update status, total_amount, order_details, and raw_payload_text.

So it behaves like: "Insert new if missing, otherwise refresh the existing order with the new values."

Here, ext_1002 exists (it was FAILED), so its status becomes PAID, and its JSON payload is replaced. Columns not listed in the update (like user_id and order_time) keep their old values.

Why this is powerful. Without upsert you would need application code like:

1. check whether the row exists
2. if not, insert
3. else, update

That needs several database round trips and more complex logic. With upsert, SQL does it in one statement.

Good to know

• The usual affected rows result is 1 for an insert, 2 for a changed existing row, and 0 when the assigned values are unchanged. Client flags matter: with CLIENT_FOUND_ROWS, an unchanged duplicate-key update can report 1 instead of 0.
• Because order_id is AUTO_INCREMENT, an upsert that turns into an update can still use up an id number, which leaves gaps in order_id. Do not expect the ids to be continuous.
• VALUES(col) still works, but it is deprecated from MySQL 8.0.20. The modern form uses a row alias:

INSERT INTO orders (external_order_id, user_id, order_time, status, total_amount)
VALUES ('ext_1002', 2, '2026-02-20 11:10:00', 'PAID', 2999.00) AS new_row
ON DUPLICATE KEY UPDATE
    status = new_row.status,
    total_amount = new_row.total_amount;

• Assignments in the UPDATE part are worked out from left to right, so a later assignment can see a column that an earlier assignment already changed.

18.2 Latest wins strategy

Basic upsert is not always enough. In distributed systems, two updates for the same record can arrive out of order:

• a user updates an order from mobile
• then updates it from a laptop
• one request reaches the server later even though it was created earlier

Which update should win? The safest answer is often: the latest event wins. That prevents older, stale data from overwriting fresher data.

 Time --->
 T1: update created first, arrives SECOND
 T2: update created second, arrives FIRST
 Without a rule: the late-arriving T1 overwrites T2  (stale data wins)
 Latest wins:    T1 is ignored, because T2 is newer

Example

INSERT INTO orders (external_order_id, user_id, order_time, status, total_amount, order_details)
VALUES (
    'ext_1003',
    2,
    '2026-02-20 00:00:00',
    'FAILED',
    2999.00,
    '{"items":[{"sku":"SKU123","qty":1,"price":2999}],"device":{"type":"desktop"},"duration_sec":"45"}'
)
ON DUPLICATE KEY UPDATE
    order_time = GREATEST(orders.order_time, VALUES(order_time)),
    status = CASE
                WHEN VALUES(order_time) >= orders.order_time THEN VALUES(status)
                ELSE orders.status
             END,
    order_details = CASE
                       WHEN VALUES(order_time) >= orders.order_time THEN VALUES(order_details)
                       ELSE orders.order_details
                    END;

Explanation

• order_time = GREATEST(orders.order_time, VALUES(order_time)) keeps the later of the two times (the old row's time and the incoming time). So order_time only moves forward, never backward.
• status = CASE ... says: if the incoming data is newer or equal, use the incoming status. If it is older, keep the old status.
• order_details = CASE ... uses the same rule: only the latest request may replace the JSON payload.

What happens with this data. ext_1003 already has order_time = 2026-02-21 09:10:00 and status PAID. The incoming row is dated 2026-02-20 00:00:00 (older) and says FAILED. So nothing changes: the order stays PAID, and the stale FAILED update is ignored.

18.3 Update only changed values

Sometimes a duplicate row arrives, but the values are exactly what is already stored. The idea: update a column only when the incoming value is really different.

Example

INSERT INTO orders (external_order_id, user_id, order_time, status, total_amount, order_details)
VALUES (
    'ext_1001',
    1,
    '2026-02-20 10:06:00',
    'PAID',
    4999.00,
    '{"items":[{"sku":"SKU123","qty":1,"price":4999}],"device":{"type":"mobile"},"duration_sec":"90"}'
)
ON DUPLICATE KEY UPDATE
    status = CASE
                WHEN NOT (orders.status <=> VALUES(status)) THEN VALUES(status)
                ELSE orders.status
             END,
    total_amount = CASE
                      WHEN NOT (orders.total_amount <=> VALUES(total_amount)) THEN VALUES(total_amount)
                      ELSE orders.total_amount
                   END;

Why use <=> here? It is MySQL's NULL-safe equality operator. Normal = gives UNKNOWN when NULL is involved. <=> compares correctly even with NULLs:

So orders.status <=> VALUES(status) asks "are these truly the same, even if one or both are NULL?" and NOT (...) means "they differ, so update".

What happens here. The row is already PAID and 4999.00, and the incoming values are the same. The CASE expressions just return the current values, so nothing changes. The statement reports 0 affected rows.

An honest note. MySQL already notices when you set a column to the value it already has and does not rewrite that column. So this CASE pattern is mostly about making your intent explicit, and it becomes truly useful when you combine it with extra rules, such as the latest-wins rule. In high-volume systems, avoiding unnecessary changes still matters for throughput, replication lag, index work, and lock time.

18.4 INSERT IGNORE

INSERT IGNORE is related to upsert, but not the same. It means: try inserting the row, and if a duplicate-key error happens, skip that row instead of failing the statement.

Unlike upsert:

• it does not update anything
• it simply ignores the duplicate

INSERT IGNORE INTO orders (external_order_id, user_id, order_time, status, total_amount, order_details)
VALUES ('ext_1001', 1, NOW(), 'PAID', 4999.00, '{"items":[]}');

Because ext_1001 already exists, nothing is inserted. The duplicate-key error becomes a warning, and the statement reports 0 rows affected.

It is useful when your rule is: "If it already exists, that is fine. Just do nothing." It is good when duplicates are harmless and you do not want them to stop the flow.

Caution. INSERT IGNORE downgrades other errors to warnings too (for example, some bad-value errors), and can quietly store adjusted values. Use it only when you are sure that is what you want.

18.5 REPLACE INTO

REPLACE INTO is often mentioned next to upsert, but it must be used with great care.

It means: if a row with the same unique key exists, delete it first, then insert the new row.

It sounds like an upsert, but it is not a gentle in-place update. It is effectively:

 delete the old row  -->  insert the new row

Why this is risky

• Auto-increment: the new row gets a new order_id, so the original id is lost.
• Lost columns: any column you do not list goes back to its default, not its old value (for example, raw_payload_text would become NULL).
• Foreign keys: deleting a row that other tables point to can fail or cascade.
• Triggers and history: delete and insert triggers both fire, and audit trails lose continuity.
• Timestamps: created-at style columns reset.
• Continuity: the original row is gone, not updated.

That is why REPLACE INTO is generally much less preferred than ON DUPLICATE KEY UPDATE.

Which one to use?

Deep Dive

Internal behavior. A JSON column stores the document in a binary format optimized for fast lookup of keys and array elements. It is validated on insert, so invalid JSON cannot get in. The format normalizes the document: extra spaces are removed and object keys are re-ordered. A JSON column cannot be indexed directly. To index a JSON value you derive a normal column from it (generated column), or use a functional or multi-valued index.

JSON or normal columns?

A common design: stable, queried fields in normal columns; flexible details in JSON; and the few JSON fields that you must filter on copied into indexed generated columns.

NULL handling (summary)

Performance considerations

• Filtering or grouping on a raw JSON path commonly scans rows unless a suitable expression, generated-column, or multi-valued index can support the predicate.
• Copy the paths you filter on into indexed generated columns.
• Do not build huge JSON documents in SELECT for large exports; use an analytics system instead.
• Large JSON values make rows bigger and updates heavier. Keep documents reasonably small.
• JSON_ARRAYAGG on very large groups creates very large values and uses lots of memory.

Database behavior

• The -> and ->> shortcuts are MySQL operators. Other databases use other tools (for example, PostgreSQL has its own -> and ->> operators on JSONB, and SQL Server uses JSON_VALUE and JSON_QUERY).
• Upsert syntax differs between databases: MySQL uses ON DUPLICATE KEY UPDATE, PostgreSQL uses INSERT ... ON CONFLICT ... DO UPDATE, and SQL Server uses MERGE.
• JSON_TABLE (MySQL 8.0.4 and later) can turn a JSON array into rows. It is a natural next topic after this one.

Real-world Use Cases

• API payload storage: keep the full request or event as JSON, and pull out the few fields you need into columns.
• Product catalogs: each product type has different attributes (size, color, voltage), kept in one JSON column.
• User profiles and settings: optional preferences that differ between users.
• Order details: items, device, coupon, and tax, stored as one payload per order (this page's example).
• Event and audit logs: append events to an array with JSON_ARRAY_APPEND.
• API-ready responses: build nested JSON directly with JSON_OBJECT and JSON_ARRAYAGG.
• Idempotent data loads: upsert by an external id, with "latest wins" to handle out-of-order events.
• Data quality checks: JSON_VALID on raw staging text, and safe casting for numbers stored as text.

Review Tips

Interviewers commonly ask:

• "What is the difference between -> and ->>?" — -> returns a JSON value (strings keep quotes). ->> returns unquoted SQL text. ->> is the same as JSON_UNQUOTE(JSON_EXTRACT(...)).
• "Why does CHAR_LENGTH(user_profile -> '$.name') give 6 for Asha?" — The extracted JSON string includes the two quotes.
• "What does JSON_VALID check?" — Only syntax, not business rules.
• "What happens when a JSON path does not exist?" — SQL NULL, not an error.
• "What is the difference between a missing key and a JSON null?" — Missing gives SQL NULL. JSON null is a real value; ->> turns it into the text null. COALESCE will not replace that text.
• "Why is CAST('abc' AS UNSIGNED) dangerous?" — It silently becomes 0. Validate first (regex), then cast.
• "Why can't JSON_SET create $.coupon.code when coupon is missing?" — The parent path must exist. Create the parent first.
• "Difference between JSON_SET, JSON_INSERT, and JSON_REPLACE?" — Set: insert or update. Insert: only if missing. Replace: only if present.
• "Why is filtering on a JSON path slow, and what is the fix?" — Row-by-row extraction and no index. Use an indexed generated column.
• "STORED vs VIRTUAL generated column?" — STORED is written to disk; VIRTUAL is computed on read. Both can be indexed in InnoDB.
• "What is upsert, and how is it done in MySQL?" — INSERT ... ON DUPLICATE KEY UPDATE.
• "Why is REPLACE INTO riskier than ON DUPLICATE KEY UPDATE?" — It deletes and re-inserts: new auto-increment id, lost columns, foreign key and trigger effects.
• "What does 'latest wins' mean, and how do you write it?" — Only let an update overwrite data if its timestamp is newer; use GREATEST and CASE.

Common Mistakes to Avoid

Mistake 1: Writing invalid JSON

Why it happens: JSON looks like a programming-language object, and habits carry over.

Wrong:

INSERT INTO users (user_id, email, created_at, user_profile)
VALUES (50, 'x@tuf.com', NOW(), "{'name': 'X'}");

Single quotes inside JSON are not allowed, and the insert fails.

Correct: '{"name": "X"}' (double quotes around keys and strings, no trailing commas).

Mistake 2: Using string functions on the output of ->

Why it happens: The value looks like plain text.

Wrong: CHAR_LENGTH(user_profile -> '$.address.city') returns 11 for Bengaluru.

Correct: Use ->> (or JSON_UNQUOTE) first, so the quotes are gone: CHAR_LENGTH(user_profile ->> '$.address.city') returns 9.

Mistake 3: Comparing values of different JSON types

Why it happens: "29" and 29 look the same.

Wrong:

SELECT user_id FROM users WHERE user_profile -> '$.age' = 29;

User 1's age is the string "29", so a JSON number 29 does not equal it. User 1 is missed.

Correct: Read as text and convert on purpose: CAST(user_profile ->> '$.age' AS UNSIGNED) = 29, or better, use the age_int generated column.

Mistake 4: Expecting ->> to give SQL NULL for a JSON null

Why it happens: "null" sounds like NULL.

Result: COALESCE(order_details ->> '$.coupon.code', 'NO_COUPON') leaves ext_1002 as the text null.

Correct: COALESCE(NULLIF(order_details ->> '$.coupon.code', 'null'), 'NO_COUPON'), or check with JSON_TYPE.

Mistake 5: Casting without validation

Why it happens: CAST(... AS UNSIGNED) seems to "just work".

Result: 'abc' becomes 0 and pollutes averages and filters.

Correct: Validate with REGEXP inside a CASE, and return NULL for bad values (section 9).

Mistake 6: Trusting JSON_VALID for business rules

Why it happens: "Valid" sounds like "correct".

Result: a payload with "duration_sec": "abc" is valid JSON but bad data.

Correct: Add separate checks for required keys and types (JSON_CONTAINS_PATH, JSON_TYPE).

Mistake 7: Using JSON_SET on a nested path whose parent is missing

Why it happens: It works when the parent exists.

Result: nothing changes, and no error tells you.

Correct: Create the parent first (JSON_SET(doc, '$.coupon', JSON_OBJECT())), then set the child. Always check the affected rows or re-read the value.

Mistake 8: Changing a number into a string by accident

Why it happens: Quotes around a value in JSON_SET.

Wrong: JSON_SET(order_details, '$.duration_sec', '100') turns the number into the string "100".

Correct: Write 100 without quotes when it must stay a number.

Mistake 9: Appending an object without converting it

Why it happens: The text looks like JSON.

Wrong: JSON_ARRAY_APPEND(order_details, '$.items', '{"sku":"SKU555"}') adds a string.

Correct: Use CAST('...' AS JSON) or JSON_OBJECT(...).

Mistake 10: Filtering or grouping on raw JSON paths in big tables

Why it happens: It works on small test data.

Result: full table scans that get slower as the table grows.

Correct: Use indexed generated columns for the paths you query often.

Mistake 11: Assuming the order inside JSON_ARRAYAGG

Why it happens: Rows often come out in a natural order in small tests.

Correct: Treat JSON_ARRAYAGG() order as unspecified in MySQL; a sorted subquery alone is not a guarantee. Use a design that explicitly guarantees the final array order, or order after retrieval.

Mistake 12: Using JSON_OBJECTAGG with duplicate or NULL keys

Why it happens: Keys come from data you do not control.

Result: an error for NULL keys, and unpredictable results for duplicates.

Correct: Pre-aggregate so keys are unique and non-NULL, as in the status-count example.

Mistake 13: Using REPLACE INTO as an upsert

Why it happens: The name sounds like "insert or replace".

Result: the old row is deleted, a new order_id is issued, and unlisted columns fall back to defaults.

Correct: Use INSERT ... ON DUPLICATE KEY UPDATE.

Mistake 14: Using INSERT IGNORE and losing real errors

Why it happens: It removes the duplicate error, which is convenient.

Result: other problems are also turned into warnings and go unnoticed.

Correct: Use it only when skipping duplicates is truly all you want, and check the warnings.

Mistake 15: Writing paths with the wrong case or index

Why it happens: SQL keywords are case-insensitive, so JSON keys feel the same.

Wrong: $.Name when the key is name, or $.items[1] when you want the first item.

Result: NULL, with no error.

Correct: JSON keys are case-sensitive, and arrays start at index 0.

Mistake 16: Searching for numbers with JSON_SEARCH

Why it happens: It sounds like a general search.

Result: it finds only string values, so a numeric 90 is never found.

Correct: Use JSON_CONTAINS or extract the value and compare.

Key Takeaways

• JSON is schema-flexible but syntax-strict: keys and strings need double quotes, and commas, braces, and brackets must be correct.
• A JSON column validates on insert and stores a normalized binary form. A text column can hold invalid JSON (a good staging area).
• JSON_VALID() checks syntax only. It does not check business rules.
• A path starts at $. Arrays are zero-indexed. Keys are case-sensitive. A missing path gives SQL NULL.
• JSON_EXTRACT() and -> return JSON values (strings keep quotes). ->> and JSON_UNQUOTE() return plain text.
• JSON keeps types: "29" (string) and 29 (number) are different.
• A missing key is SQL NULL. A JSON null is a real value, and ->> turns it into the text null, which COALESCE does not replace.
• CAST of bad text silently becomes 0. Validate first with REGEXP, then cast, and return NULL for bad values.
• JSON_SEARCH finds string values and returns paths (% and _ are wildcards, and an escape character makes them literal). JSON_CONTAINS checks whether a document contains a value or object.
• JSON_SET inserts or updates, JSON_INSERT only inserts, JSON_REPLACE only updates, JSON_REMOVE deletes, and JSON_ARRAY_APPEND adds to the end of an array. The parent path must exist for a nested JSON_SET.
• JSON_OBJECT, JSON_ARRAYAGG, and JSON_OBJECTAGG build JSON from rows. Use them for small, well-shaped responses, not for huge exports.
• Filtering or grouping on raw JSON paths commonly causes row-by-row extraction and scans without a suitable expression, generated-column, or multi-valued index. An indexed generated column is one MySQL solution, with added write and index-maintenance cost.
• Upsert = insert, or update if the key already exists. In MySQL, use INSERT ... ON DUPLICATE KEY UPDATE.
• "Latest wins" uses GREATEST and CASE on a timestamp, so stale updates cannot overwrite fresh data.
• INSERT IGNORE skips duplicates (and hides other errors). REPLACE INTO deletes and re-inserts, so prefer ON DUPLICATE KEY UPDATE.