JSON_SET() vs JSON_INSERT() vs JSON_REPLACE() in MySQL: What’s the Difference?

by

In MySQL, there are a number of functions to help you work with JSON documents.  These include the JSON_SET(), JSON_INSERT(), and JSON_REPLACE() functions.

These three functions are related, yet they do differ slightly.

Syntax

First, here’s the syntax for all three functions:

JSON_SET(json_doc, path, val[, path, val] ...)
JSON_INSERT(json_doc, path, val[, path, val] ...)
JSON_REPLACE(json_doc, path, val[, path, val] ...)

So all three functions accept the same arguments. Here’s what these arguments are for:

  • json_doc is the JSON document.
  • path is the path of the element for which to insert data or update the value at.
  • val is the new value.

The Difference

Here’s the difference between these functions:

So basically which one you use depends on whether you’re updating an existing value or inserting a new one (although JSON_SET() does both).

Example 1 – Insert a Value

Here are examples to demonstrate the difference between these three functions.

JSON_SET()

Here’s what happens if we try to insert a new value using JSON_SET():

SELECT JSON_SET('{"a": 1, "b": 2}', '$.c', 3) AS 'Result';

Result:

+--------------------------+
| Result                   |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+

So it worked perfectly.

JSON_INSERT()

Here’s what happens if we try to insert a new value using JSON_INSERT():

SELECT JSON_INSERT('{"a": 1, "b": 2}', '$.c', 3) AS 'Result';

Result:

+--------------------------+
| Result                   |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+

Exactly the same result as with JSON_SET().

JSON_REPLACE()

Here’s what happens if we try to insert a new value using JSON_REPLACE():

SELECT JSON_REPLACE('{"a": 1, "b": 2}', '$.c', 3) AS 'Result';

Result:

+------------------+
| Result           |
+------------------+
| {"a": 1, "b": 2} |
+------------------+

In this case, the new value wasn’t inserted. The original JSON document is returned unchanged. This is because this function only replaces existing values – it doesn’t insert new ones.

Example 2 – Update an Existing Value

Now for updating existing values.

JSON_SET()

Here’s what happens if we try to update an existing value using JSON_SET():

SELECT JSON_SET('{"a": 1, "b": 2}', '$.b', 3) AS 'Result';

Result:

+------------------+
| Result           |
+------------------+
| {"a": 1, "b": 3} |
+------------------+

So again, it worked perfectly. We successfully updated the second key/value pair with the new value.

JSON_INSERT()

Here’s what happens if we try to update an existing value using JSON_INSERT():

SELECT JSON_INSERT('{"a": 1, "b": 2}', '$.b', 3) AS 'Result';

Result:

+------------------+
| Result           |
+------------------+
| {"a": 1, "b": 2} |
+------------------+

In this case the existing value wasn’t updated. The JSON document is returned unchanged. This is because the JSON_INSERT() function only inserts new values – it doesn’t update existing ones.

JSON_REPLACE()

Here’s what happens if we try to update an existing value using JSON_REPLACE():

SELECT JSON_REPLACE('{"a": 1, "b": 2}', '$.b', 3) AS 'Result';

Result:

+------------------+
| Result           |
+------------------+
| {"a": 1, "b": 3} |
+------------------+

It updates perfectly.