Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)0%

Indexing JSON via Virtual Generated Columns

Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)

Advanced13 min read•Updated: 2026-09-12

Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)

Updating JSON documents does not require overwriting the entire document. MySQL provides dedicated JSON manipulation functions that surgically alter keys, values, and arrays directly inside the database engine.

The Three Core Mutation Functions: SET vs INSERT vs REPLACE

Function	If Key Already Exists	If Key Does NOT Exist
`JSON_SET()`	Overwrites existing value	Creates new key/value
`JSON_INSERT()`	Ignores (Does not change)	Creates new key/value
`JSON_REPLACE()`	Overwrites existing value	Ignores (Does not create)

1. Updating with JSON_SET()

JSON_SET is the most widely used modifier because it handles both updates and insertions (upsert behavior):

1-- Update warranty to 3 years AND add a new 'color' property:
2UPDATE product_catalogs
3SET attributes = JSON_SET(
4    attributes,
5    '$.warranty_years', 3,           -- Overwrites existing key
6    '$.color', 'Space Gray'          -- Inserts new key!
7)
8WHERE product_id = 1;

2. Safeguarding Existing Keys with JSON_INSERT()

JSON_INSERT only writes if the path is currently missing:

1-- Add 'refurbished': false only if it hasn't already been defined
2UPDATE product_catalogs
3SET attributes = JSON_INSERT(attributes, '$.refurbished', false)
4WHERE product_id = 1;

3. Deleting Keys with JSON_REMOVE()

JSON_REMOVE deletes one or more keys or array elements:

1-- Remove the 'warranty_years' property and the 2nd tag in the array
2UPDATE product_catalogs
3SET attributes = JSON_REMOVE(
4    attributes, 
5    '$.warranty_years',
6    '$.tags[1]'
7)
8WHERE product_id = 1;

4. Working with Arrays: JSON_ARRAY_APPEND()

To append elements to an existing nested array:

1-- Append 'extended-battery' to the product's tags array
2UPDATE product_catalogs
3SET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'extended-battery')
4WHERE product_id = 1;

Partial In-Place Updates in MySQL 8.0

In MySQL 8.0, if an update to a JSON column using JSON_SET or JSON_REPLACE only changes existing values without expanding the document size, InnoDB performs a partial in-place update. Instead of rewriting the entire JSON document to disk, it updates only the modified bytes in the redo log and data page, slashing write I/O.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which function updates an existing JSON key or creates it if it does not exist?

Question 2

What does JSON_INSERT(json, '$.status', 'active') do if $.status already contains 'pending'?

Question 3

Which function permanently deletes a property or array element from a JSON document?

Question 4

How do you append a new element to an existing JSON array in MySQL?

Question 5

What performance optimization does MySQL 8.0 apply when updating small values in-place within a JSON column?

Next Lesson

Indexing JSON via Virtual Generated Columns

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Storing & Querying Native JSON in MySQL	Indexing JSON via Virtual Generated Columns

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Indexing JSON via Virtual Generated Columns

Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)

Advanced13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)

The Three Core Mutation Functions: SET vs INSERT vs REPLACE

Function	If Key Already Exists	If Key Does NOT Exist
`JSON_SET()`	Overwrites existing value	Creates new key/value
`JSON_INSERT()`	Ignores (Does not change)	Creates new key/value
`JSON_REPLACE()`	Overwrites existing value	Ignores (Does not create)

1. Updating with JSON_SET()

JSON_SET is the most widely used modifier because it handles both updates and insertions (upsert behavior):

1-- Update warranty to 3 years AND add a new 'color' property:
2UPDATE product_catalogs
3SET attributes = JSON_SET(
4    attributes,
5    '$.warranty_years', 3,           -- Overwrites existing key
6    '$.color', 'Space Gray'          -- Inserts new key!
7)
8WHERE product_id = 1;

2. Safeguarding Existing Keys with JSON_INSERT()

JSON_INSERT only writes if the path is currently missing:

1-- Add 'refurbished': false only if it hasn't already been defined
2UPDATE product_catalogs
3SET attributes = JSON_INSERT(attributes, '$.refurbished', false)
4WHERE product_id = 1;

3. Deleting Keys with JSON_REMOVE()

JSON_REMOVE deletes one or more keys or array elements:

1-- Remove the 'warranty_years' property and the 2nd tag in the array
2UPDATE product_catalogs
3SET attributes = JSON_REMOVE(
4    attributes, 
5    '$.warranty_years',
6    '$.tags[1]'
7)
8WHERE product_id = 1;

4. Working with Arrays: JSON_ARRAY_APPEND()

To append elements to an existing nested array:

1-- Append 'extended-battery' to the product's tags array
2UPDATE product_catalogs
3SET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'extended-battery')
4WHERE product_id = 1;

Partial In-Place Updates in MySQL 8.0

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which function updates an existing JSON key or creates it if it does not exist?

Question 2

What does JSON_INSERT(json, '$.status', 'active') do if $.status already contains 'pending'?

Question 3

Which function permanently deletes a property or array element from a JSON document?

Question 4

How do you append a new element to an existing JSON array in MySQL?

Question 5

What performance optimization does MySQL 8.0 apply when updating small values in-place within a JSON column?

Next Lesson

Indexing JSON via Virtual Generated Columns

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Storing & Querying Native JSON in MySQL	Indexing JSON via Virtual Generated Columns

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next