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.
Author: Ian
JSON_SET() – Insert or Update Values in a JSON Document in MySQL
In MySQL, the JSON_SET() function inserts or updates values in a JSON document and returns the result.
You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert. You can provide multiple path/value pairs if you need to update multiple values.
JSON_REPLACE() – Replace Values in a JSON Document in MySQL
In MySQL, the JSON_REPLACE() function replaces values in a JSON document and returns the result.
You provide the JSON document as the first argument, followed by the path to replace data at, followed with the value to replace the existing value with.
You can replace data at multiple places in the document if required. To do this, simply provide multiple path/value pairs as required.
JSON_REMOVE() – Remove Data from a JSON Document in MySQL
In MySQL, the JSON_REMOVE() function removes data from a JSON document and returns the result.
You provide the JSON document as the first argument, followed by the path to remove data from. You can provide multiple paths if required.
JSON_MERGE_PATCH() vs JSON_MERGE_PRESERVE() in MySQL: What’s the Difference?
MySQL includes a number of functions for working with JSON documents. Among these are the JSON_MERGE_PATCH() and JSON_MERGE_PRESERVE() functions.
Both of these functions merge two or more JSON documents and return the result. However, there are certain cases where these functions will return a different result. You need to be aware of this before including them in any of your queries.
JSON_MERGE_PRESERVE() – Merge Multiple JSON Documents in MySQL
In MySQL, the JSON_MERGE_PRESERVE() function merges two or more JSON documents and returns the result.
You provide the JSON documents as arguments.
This function was added in MySQL 8.0.3 as a synonym for JSON_MERGE(), however, the JSON_MERGE() function is now deprecated, and is subject to removal in a future release of MySQL.
JSON_MERGE_PATCH() – Perform an RFC 7396 Compliant Merge of JSON Documents in MySQL
In MySQL, the JSON_MERGE_PATCH() function performs an RFC 7396 compliant merge of two or more JSON documents, without preserving members having duplicate keys.
You provide the JSON documents as arguments.
JSON_INSERT() – Insert Values into a JSON Document in MySQL
In MySQL, the JSON_INSERT() function inserts a value into a JSON document and returns the result.
You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert.
JSON_ARRAY_INSERT() – Insert Values into a JSON Array in MySQL
In MySQL, the JSON_ARRAY_INSERT() function is used to insert a new value into an array.
You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert.
JSON_ARRAY_APPEND() – Append Values to a JSON Array in MySQL
When using JSON documents with MySQL, we can use the JSON_ARRAY_APPEND() function to append new values to an array.
The way it works is, you provide the JSON document as the first argument, then follow that up with the path to append to, followed by the value to append.
In MySQL 5.7, this function was called JSON_APPEND() but that name is no longer supported.