The MySQL JSON_MERGE function is used to combine two or more JSON documents into a single document. It is a versatile tool for working with JSON data in MySQL, enabling you to merge nested structures, arrays, and scalar values. The function was introduced in MySQL 5.7.22 and has been deprecated since MySQL 8.0.3. However, it is still widely used and serves as a fundamental step in many JSON data manipulation tasks.

Syntax

The basic syntax of the JSON_MERGE function is as follows:

JSON_MERGE(json_doc, json_doc[, ...])

json_doc: The JSON documents to be merged. You can specify two or more JSON documents separated by commas.

Example

Let’s consider a simple example where we have two JSON documents and we want to merge them using the JSON_MERGE function.

SELECT JSON_MERGE(
'{"name": "John", "age": 30, "city": "New York"}',
'{"occupation": "Software Engineer", "salary": 80000}'
) AS merged_json;

In this example, we are merging two JSON documents. The result of the JSON_MERGE function will be a new JSON document that combines the key-value pairs from both input documents. The output would be:

{
  "name": "John",
  "age": 30,
  "city": "New York",
  "occupation": "Software Engineer",
  "salary": 80000
}

It’s important to note that if there are any overlapping keys in the input JSON documents, the values from the later documents will overwrite the values from the earlier documents. In the merged JSON, duplicate keys are not allowed.

SELECT JSON_MERGE(
'{"name": "John", "age": 30, "city": "New York"}',
'{"age": 35, "occupation": "Software Engineer", "salary": 80000}'
) AS merged_json;

The output in this case would be:

{
  "name": "John",
  "age": 35,
  "city": "New York",
  "occupation": "Software Engineer",
  "salary": 80000
}

In the merged JSON, the age key takes the value from the second JSON document, overwriting the value from the first document.

Alternatives to JSON_MERGE

As mentioned earlier, the JSON_MERGE function has been deprecated. MySQL provides two alternative functions for merging JSON documents:

JSON_MERGE_PRESERVE: This function behaves similarly to JSON_MERGE, preserving duplicate keys and creating arrays of arrays or objects with multiple values for the same key.

JSON_MERGE_PATCH: This function implements the JSON Merge Patch algorithm specified in RFC 7396. It overwrites existing values instead of appending them, making it more suitable for updating JSON documents.

The choice between JSON_MERGE_PRESERVE and JSON_MERGE_PATCH depends on the specific requirements of the application. If you need to preserve duplicate keys, JSON_MERGE_PRESERVE is the better choice. If you want to update existing values, JSON_MERGE_PATCH is more appropriate.