MySQL Blog Archive
For the latest blogs go to
Proposal to Change the Behavior of JSON_MERGE

In MySQL 5.7, one of the most popular new features is the introduction of JSON:

  1. The JSON data type
  2. A set of 20 functions to search, manipulate and create JSON documents
  3. Virtual Columns for JSON indexing

In MySQL 8.0 we plan to extend this functionality (for example: with the addition of JSON aggregate functions), but we are also considering changing the behaviour of one of the existing functions.

The JSON_MERGE Function

In MySQL 5.7 the JSON merge function has the following semantics when two objects are merged with overlapping values:

That is to say that 'a' was converted to an array with both values present.

We have received feedback from a number of users that a merge function would be more useful if it were to instead use precedence of last value wins:

This is consistent with several scripting languages. For example in PHP there is an array_merge function where the last value takes precedence. The JSON_MERGE function in MySQL is more similar to PHP’s array_merge_recursive:

Duplicate (key) Names

On a related point, the JavaScript Object Notation (JSON) Data Interchange Format (RFC7159) says that:

The names within an object SHOULD be unique.

That is to say that duplicates are not supposed to happen (but not that duplicates must not be present). A lot is left up to the implementation on how to handle the non-unique names. In MySQL 5.7 the current behavior is to use the first key:

To use PHP as an example again, it will use the last key:

Proposal to Change Behavior

We are considering redefining the behavior of JSON_MERGE to be more consistent with the expected behaviour, while retaining the current functionality under a different function name (i.e. JSON_MERGE_ARRAY or JSON_MERGE_RECURSIVE).  In addition, we are also considering the behavior of duplicate key names.

Redefining functionality makes upgrades harder, so it is a decision that we would like external feedback on before moving forward on.

Please leave a comment, or get in touch!