JSON functions return incorrect values when an object key path is used on an array value.
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
JSON_VALUE and JSON_EXTRACT allow you to extract a value from a JSON document at a specified path. We currently use https://github.com/dolthub/jsonpath to parse these paths, which is built according to https://goessner.net/articles/JsonPath/
However, MySQL's JSONPath syntax differs from this spec, leading to differences in behavior. One of these differences happens when a path containing an object key is used to look up within in array.
In MySQL, this lookup fails.
JSONPath will recursively search for the path in each array element, and return a result array containing all matches.
Examples of different behavior between MySQL and Dolt:
Dolt:
```sql
analyze/main*> select JSON_EXTRACT('[{"a": 1}, {"a": 2}]', "$.a");
+---------------------------------------------+
| JSON_EXTRACT('[{"a": 1}, {"a": 2}]', "$.a") |
+---------------------------------------------+
| [1, 2] |
+---------------------------------------------+
1 row in set (0.00 sec)
analyze/main*> select JSON_EXTRACT('[{"a": [{"b": 1}, {"b": 2}]}, {"a": [{"b": 3}, {"b": 4}]}]', "$.a.b");
+-------------------------------------------------------------------------------------+
| JSON_EXTRACT('[{"a": [{"b": 1}, {"b": 2}]}, {"a": [{"b": 3}, {"b": 4}]}]', "$.a.b") |
+-------------------------------------------------------------------------------------+
| [[1, 2], [3, 4]] |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
MySQL:
```sql
mysql> select JSON_EXTRACT('[{"a": 1}, {"a": 2}]', "$.a");
+---------------------------------------------+
| JSON_EXTRACT('[{"a": 1}, {"a": 2}]', "$.a") |
+---------------------------------------------+
| NULL |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_EXTRACt('[{"a": [{"b": 1}, {"b": 2}]}, {"a": [{"b": 3}, {"b": 4}]}]', "$.a.b");
+-----------------------------------------------------------------------------------+
| JSON_VALUE('[{"a": [{"b": 1}, {"b": 2}]}, {"a": [{"b": 3}, {"b": 4}]}]', "$.a.b") |
+-----------------------------------------------------------------------------------+
| NULL |
+-----------------------------------------------------------------------------------+
```
This behavior is identical for JSON_VALUE.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.