MySQL :: Proposal to Change the Behavior of JSON

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

The JSON data type
A set of 20 functions to search, manipulate and create JSON documents
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:

mysql> SELECT JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }'); 
+-----------------------------------------------------+ 
| JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') | 
+-----------------------------------------------------+ 
| {"a": [1, 3], "b": 2, "c": 4}                       |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');

+-----------------------------------------------------+

| JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |

+-----------------------------------------------------+

| {"a": [1, 3], "b": 2, "c": 4} |

+-----------------------------------------------------+

1 row in set (0.00 sec)

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:

mysql> SELECT JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }'); 
+-----------------------------------------------------+ 
| JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') | 
+-----------------------------------------------------+ 
| {"a": 3, "b": 2, "c": 4}                       |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');

+-----------------------------------------------------+

| JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |

+-----------------------------------------------------+

| {"a": 3, "b": 2, "c": 4} |

+-----------------------------------------------------+

1 row in set (0.00 sec)

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:

<?php

$a1 = array('a' => array('b' => 1, 'c' => 2));
$a2 = array('a' => array('b' => 3, 'd' => 4));

echo 'array_merge:', PHP_EOL;
echo json_encode(array_merge($a1, $a2), JSON_PRETTY_PRINT);

echo PHP_EOL, PHP_EOL, 'array_merge_recursive:', PHP_EOL;
echo json_encode(array_merge_recursive($a1, $a2), JSON_PRETTY_PRINT);

?>

array_merge:
{
    "a": {
        "b": 3,
        "d": 4
    }
}

array_merge_recursive:
{
    "a": {
        "b": [
            1,
            3
        ],
        "c": 2,
        "d": 4
    }
}

<?php

$a1 = array('a' => array('b' => 1, 'c' => 2));

$a2 = array('a' => array('b' => 3, 'd' => 4));

echo 'array_merge:', PHP_EOL;

echo json_encode(array_merge($a1, $a2), JSON_PRETTY_PRINT);

echo PHP_EOL, PHP_EOL, 'array_merge_recursive:', PHP_EOL;

echo json_encode(array_merge_recursive($a1, $a2), JSON_PRETTY_PRINT);

array_merge:

{

"a": {

"b": 3,

"d": 4

}

array_merge_recursive:

{

"a": {

"b": [

"c": 2,

"d": 4

}

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:

mysql> select cast('{"a":{"b":1, "b":2}}' as json);
+--------------------------------------+
| cast('{"a":{"b":1, "b":2}}' as json) |
+--------------------------------------+
| {"a": {"b": 1}}                      |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('{"a":{"b":1, "b":2}}' as json);

+--------------------------------------+

| cast('{"a":{"b":1, "b":2}}' as json) |

+--------------------------------------+

| {"a": {"b": 1}} |

+--------------------------------------+

1 row in set (0.00 sec)

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

<?php echo json_encode(json_decode('{"a":{"b":1, "b":2}}')); ?>
{"a":{"b":2}}

1 2	<?php echo json_encode(json_decode('{"a":{"b":1, "b":2}}')); ?> {"a":{"b":2}}

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!