| 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: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 8.0.2 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.