dolthub / dolthub/dolt

JSON functions return incorrect values when an object key path is used on an array value.

Closed
#7,905 0 comments 0 reactions 1 assignee Claimed by @zachmu View on GitHub
sql
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.