Proposal to Change the Behavior of JSON_MERGE

存储架构 2017-04-10

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);
    "a": {
        "b": 3,
        "d": 4
    "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)

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
). 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

责编内容by:MySQL Server Blog (源链)。感谢您的支持!


Webinar Thursday June 22, 2017: Deploying MySQL in... Join Percona’s Senior Operations Engineer, Daniel Kowalewski as he presents...
Rsyslog+Loganalyer+MySQL下部署日志服务器 准备工作( 三台CentOS服务器Centos7 ): MySQL服务器(收集储存管理日志 web/Rsyslog服...
提高MySQL性能的7个技巧 原文: 7 keys to better MySQL performance 作者:Peter Zaitsev 译者:Peter 译者注: 随着...
MySQL · myrocks · 相关tools介绍 概述 MyRocks提供了丰富的tools,如sst_dump, mysql_ldb等,这些工具对我们的运维和分析问题非常有用。 sst_dump 可以...
Python and Compose – Scylla and Compose for ... Hello, and welcome back to the final Python leg of the Compose Grand Tour. The p...