WL#9692: Add JSON_MERGE_PATCH, rename JSON_MERGE to JSON_MERGE_PRESERVE

Affects: Server-8.0   —   Status: Complete

Currently JSON_MERGE merges two JSON documents by including duplicates:

JSON_MERGE('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }'); # returns { "a": [1,3], "b":2, "c": 4}

The semantics of JavaScript (and other scripting languages) would be to use precedence of the second document. This is proposed in BUG#81283 as a new function.

To support different use cases and minimize confusion among users, the following steps are proposed:

  1. Create a new JSON_MERGE_PATCH() function that supports behavior familiar to PHP users and implementors of REST services, and is specified by https://tools.ietf.org/html/rfc7396

  2. Introduce a new JSON_MERGE_PRESERVE() function that is an alias to the existing JSON_MERGE() function

  3. Deprecate the existing JSON_MERGE() function in MySQL 8.0, to remove ambiguity for the merge operation.

Functional requirements

F-1: When the JSON_MERGE function is used in a query, a deprecation warning should be raised when the query is prepared.

F-2: When the function JSON_MERGE_PRESERVE is called, it should behave the same way as JSON_MERGE, except that it doesn't raise a deprecation warning.

F-3: If JSON_MERGE_PATCH is called with less than two arguments, an error should be raised.

F-4: If one of the arguments to JSON_MERGE_PATCH is NULL (the SQL unknown value), and all of the arguments after the last NULL argument are JSON objects, the result of the function should be NULL.

F-5: If one of the arguments to JSON_MERGE_PATCH is not of type JSON and is not a valid JSON text, an error should be raised.

F-6: If JSON_MERGE_PATCH is called with more than two arguments, it should merge the first two arguments, and then successively merge the next argument into the result of the previous merge. For example, JSON_MERGE_PATCH(arg1, arg2, arg3, arg4) is equivalent to JSON_MERGE_PATCH(JSON_MERGE_PATCH(JSON_MERGE_PATCH(arg1, arg2), arg3), arg4).

F-7: If JSON_MERGE_PATCH is called with two non-NULL arguments that are valid JSON values, it should return the value given by the algorithm described in RFC 7396:

define MergePatch(Target, Patch):
  if Patch is an Object:
    if Target is not an Object:
      Target = {} # Ignore the contents and set it to an empty Object
    for each Key/Value pair in Patch:
      if Value is null:
        if Key exists in Target:
          remove the Key/Value pair from Target
      else:
        Target[Key] = MergePatch(Target[Key], Value)
    return Target
  else:
    return Patch

Changes in the JSON_MERGE function

JSON_MERGE is changed to raise a warning when the statement is prepared, suggesting that JSON_MERGE is deprecated, and that JSON_MERGE_PRESERVE or JSON_MERGE_PATCH should be used instead.

New function: JSON_MERGE_PRESERVE

The new function JSON_MERGE_PRESERVE behaves the same way as JSON_MERGE currently does. The only difference between JSON_MERGE and JSON_MERGE_PRESERVE is that the former will raise a deprecation warning.

A note about the name "JSON_MERGE_PRESERVE": The original proposal was "JSON_MERGE_RECURSIVE". But JSON_MERGE_PATCH also uses a recursive algorithm, so it is not very descriptive. The "PRESERVE" suffix is meant to signal that the result always preserves all data from the original documents. This is in contrast to JSON_MERGE_PATCH, which could replace or remove some of the original data.

New function: JSON_MERGE_PATCH

This function merges two or more json documents into a single document, using the merge algorithm specified in RFC 7396.

JSON JSON_MERGE_PATCH(ANY_JSON doc, ANY_JSON doc2 [ (, ANY_JSON otherdocs )* ])

Returns:

  • unknown - Returns NULL if any of the arguments are NULL and all of the arguments after the last NULL argument are JSON objects.

  • document - Otherwise returns a sensible JSON value produced by merging the documents as described below.

Behavior:

  • invalid - Raises an error if any of the documents isn't valid.

  • If the first argument is not an object, the merge result is the same as if an empty object had been merged with the second argument.

  • If the second argument is not an object, the merge result is the second argument.

  • If both arguments are objects, the merge result is an object with the following members:

    • all members of the first object which do not have a corresponding member with the same key in the second object.

    • all members of the second object that don't have a corresponding key in the first object, and whose value is not the JSON null literal.

    • all members with a key that exists in both the first and the second object, and whose value in the second object is not the JSON null literal. The values of these members are the results of recursively merging the value in the first object with the value in the second object.

For example:

# Returns {"a":"c"}
SELECT JSON_MERGE_PATCH('{"a":"b"}', '{"a":"c"}');

# Returns {"a":"b", "b":"c"}
SELECT JSON_MERGE_PATCH('{"a":"b"}', '{"b":"c"}');

# Returns {}
SELECT JSON_MERGE_PATCH('{"a":"b"}', '{"a":null}');

# Returns {"b":"c"}
SELECT JSON_MERGE_PATCH('{"a":"b", "b":"c"}', '{"a":null}');

# Returns {"a":"c"}
SELECT JSON_MERGE_PATCH('{"a":["b"]}', '{"a":"c"}');

# Returns {"a":["b"]}
SELECT JSON_MERGE_PATCH('{"a":"c"}', '{"a":["b"]}');

# Returns {"a": {"b":"d"}}
SELECT JSON_MERGE_PATCH('{"a": {"b":"c"}}',
                        '{"a": {"b":"d", "c":null}}');

# Returns {"a": [1]}
SELECT JSON_MERGE_PATCH('{"a":[{"b":"c"}]}', '{"a": [1]}');

# Returns ["c","d"]
SELECT JSON_MERGE_PATCH('["a","b"]', '["c","d"]');

# Returns ["c"]
SELECT JSON_MERGE_PATCH('{"a":"b"}', '["c"]');

# Returns the JSON null literal
SELECT JSON_MERGE_PATCH('{"a":"foo"}', 'null');

# Returns "bar"
SELECT JSON_MERGE_PATCH('{"a":"foo"}', '"bar"');

# Returns {"e":null, "a":1}
SELECT JSON_MERGE_PATCH('{"e":null}', '{"a":1}');

# Returns {"a":"b"}
SELECT JSON_MERGE_PATCH('[1,2]', '{"a":"b", "c":null}');

# Returns {"a": {"bb":{}}}
SELECT JSON_MERGE_PATCH('{}', '{"a":{"bb":{"ccc":null}}}');

# Returns UNKNOWN
SELECT JSON_MERGE_PATCH(NULL, '{}');

# Returns UNKNOWN
SELECT JSON_MERGE_PATCH('{}', NULL);

# Returns UNKNOWN
SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}');

# Returns [1,2,3]
SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]');

# Returns {"d":"e"}
SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}');

The Item_func_json_merge class should be renamed to Item_func_merge_preserve, and should be wired into the parser by adding it to func_array in item_create.cc.

A new class named Item_func_json_merge should be added. It should inherit from Item_func_json_merge_preserve and not override any methods. Its constructor should raise an ER_WARN_DEPRECATED_SYNTAX warning.

A new class named Item_func_json_merge_patch should be added, whose val_json() method implements the JSON_MERGE_PATCH function. The class should be wired into the parser by adding it to func_array in item_create.cc.



Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.