Bug #71809 When using json_merge it duplicates key's instead of updating the new value

• Submitted: 23 Feb 2014 12:49
• Modified: 30 Jul 2014 19:52
• Reporter: Yaniv Hakim
• Status: Closed
• Category: MySQL Server: JSON User-defined function ( UDF )
• Severity: S1 (Critical)
• Version: 3.0
• OS: Any
• Tags: json, json_merge, udf
• Triage: Needs Triage: D5 (Feature request)

[23 Feb 2014 12:49] Yaniv Hakim

Description:
Currently when merging two json docs using json_merge it duplicates key's and values that are present on both the docs being merged instead of just updates the value with the new doc.

This is causing the returned doc to be non serializable and i think non legal json. 

for example: 
json_merge({"a":"b"}, {"a":"c"}) returns: {"a":"b","a":"c"} instead of:{"a":"c"}

another example:
json_merge('{"a":{"x":"y"},"b":1}', '{"a":{"x":"z"},"b":1,"c":2}')
returns {"a":{"x":"y"},"b":1, "a":{"x":"z"},"b":1,"c":2}
instead of {"a":{"x":"z"},"b":1, "c":2}

How to repeat:
example: 
json_merge({"a":"b"}, {"a":"c"}) returns: {"a":"b","a":"c"} instead of:{"a":"c"}

another example:
json_merge('{"a":{"x":"y"},"b":1}', '{"a":{"x":"z"},"b":1,"c":2}')
returns {"a":{"x":"y"},"b":1, "a":{"x":"z"},"b":1,"c":2}
instead of {"a":{"x":"z"},"b":1, "c":2}

Suggested fix:
what we need is a function that acts like json_set but for two json docs..
or just fix json_merge to do that because i can't see why someone will use it as it is now.

[2 Apr 2014 9:51] Roland Bouman

Yaniv, 

while this issue is being considered, you might want to try out mysqlv8udfs: https://github.com/rpbouman/mysqlv8udfs

With mysqlv8udfs, your requirement can be met as such:

mysql> select jsudf('require("json_merge.js");', '{"a":{"x":"y"},"b":1}', '{"a":{"x":"z"},"b":1,"c":2}');
+--------------------------------------------------------------------------------------------+
| jsudf('require("json_merge.js");', '{"a":{"x":"y"},"b":1}', '{"a":{"x":"z"},"b":1,"c":2}') |
+--------------------------------------------------------------------------------------------+
| {
 "a": {
  "x": "z"
 },
 "b": 1,
 "c": 2
}                                                |
+--------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

Code for json_merge.js:

(function(){

  this.init = function(){
    if (this.arguments.length < 2) {
      throw "You must supply at least 2 arguments.";
    }
  };

  function merge(dest, src){
    if (typeof(src) !== "object") {
      return;
    }
    else
    if (dest instanceof Array && src instanceof Array) {
      var i, n = src.length;
      for (i = 0; i < n; i++) {
        dest.push(src[i]);
      }
      return;
    }
    var prop, val;
    for (prop in src) {
      val = src[prop];
      switch (typeof(dest[prop])) {
        case "object":
          merge(dest[prop], val);
          break;
        default:
          dest[prop] = val;
          break;
      }
    }
  }

  this.udf = function(){
    var doc = JSON.parse(arguments[0]);
    var o = doc, i, n = arguments.length;
    for (i = 1; i < n; i++) {
      merge(doc, JSON.parse(arguments[i]));
    }
    return JSON.stringify(doc, null, " ");
  }

})();

[30 Jul 2014 19:52] Sveta Smirnova

Posted by developer:
 
Fixed in version 0.3.2