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:

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)

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
:

 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
    }
}

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)

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

{"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!

MySQL Server Blog稿源:MySQL Server Blog (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Proposal to Change the Behavior of JSON_MERGE

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录