Bug #86866 Use last-wins strategy for duplicate keys in JSON objects
Submitted: 29 Jun 2017 9:09 Modified: 2 Aug 2017 7:42
Reporter: Knut Anders Hatlen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0.2 OS:Any
Assigned to: CPU Architecture:Any

[29 Jun 2017 9:09] Knut Anders Hatlen
Description:
If a JSON object contains multiple members with the same key name, MySQL will keep the first member and discard the rest.

RFC 7159, which is the specification of the JSON format used by MySQL, says the following about duplicate key names:

  When the names within an object are not unique, the behavior of
  software that receives such an object is unpredictable. Many
  implementations report the last name/value pair only. Other
  implementations report an error or fail to parse the object, and
  some implementations report all of the name/value pairs, including
  duplicates.

Keeping the last name/value pair is mentioned as an option, but not keeping the first pair. It would be less surprising if MySQL used one of the behaviours mentioned in the RFC.

JSON is based on JavaScript, and if a JSON text is evaluated in JavaScript, the last name/value pair is kept if multiple pairs with the same name are specified.

How to repeat:
SELECT CAST('{"a": 1, "a": 2}' AS JSON) returns {"a": 1} rather than {"a": 2}.

Suggested fix:
Use the last name/value pair in case a JSON object has multiple pairs with the same name.
[2 Aug 2017 7:42] Jon Stephens
Documented fix as follows in the MySQL 8.0.3 changelog:
  
  If a JSON object contained multiple members with the same key
  name, MySQL kept the first member and discarded the remainder.
  This contradicts RFC 7159, which suggests that duplicate key
  names can be handled in one of the ways listed here:

      Report an error (or otherwise fail to parse the object)
    
      Report all of the name-value pairs, including duplicates
    
      Report the last name-value pair only

  When a JSON text is evaluated in JavaScript, the last
  name-value pair is kept if multiple pairs with the same name are
  specified. MySQL now does likewise, and implements the last of the 
  three options just listed, as shown here:

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES ('{"x": 17, "x": "red", "x": [3, 5, 7]}');
                                                                                                                                                                                                              
mysql> SELECT c1 FROM t1;
+------------------+
| c1               |
+------------------+
| {"x": [3, 5, 7]} |
+------------------+

  The fix for this issue also corrects a failure in the MySQL 8.0
  server to handle insertion into a JSON column of data containing
  JSON arrays as the values for multiple identical keys.
  

Closed.