Incorrect results for JSON lookups.
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
According to the MySQL documentation for JSON paths (https://dev.mysql.com/doc/refman/8.4/en/json.html):
- [N] appended to a path that selects an array names the value at position N within the array. Array positions are integers beginning with zero. If path does not select an array value, path[0] evaluates to the same value as path.
The following example queries demonstrate this:
```sql
mysql> select JSON_VALUE('{"a": 1}', "$[0].a");
+----------------------------------+
| JSON_VALUE('{"a": 1}', "$[0].a") |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.01 sec)
mysql> select JSON_VALUE('{"a": 1}', "$[0].a[0]");
+-------------------------------------+
| JSON_VALUE('{"a": 1}', "$[0].a[0]") |
+-------------------------------------+
| 1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_VALUE('{"a": 1}', "$.a[0]");
+----------------------------------+
| JSON_VALUE('{"a": 1}', "$.a[0]") |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
```
However, all of these return errors in Dolt:
```sql
jtest/main*> select JSON_VALUE('{"a": 1}', "$[0].a");
object is not Slice
jtest/main*> select JSON_VALUE('{"a": 1}', "$.a[0]");
object is not Slice
jtest/main*> select JSON_VALUE('{"a": 1}', "$[0].a[0]");
object is not Slice
```
Functions the modify JSON documents have similar behavior. This is the MySQL behavior:
```sql
mysql> select JSON_SET('{"a": 1}', "$.a[0]", 2);
+-----------------------------------+
| JSON_SET('{"a": 1}', "$.a[0]", 2) |
+-----------------------------------+
| {"a": 2} |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_SET('{"a": 1}', "$[0].a[0]", 2);
+--------------------------------------+
| JSON_SET('{"a": 1}', "$[0].a[0]", 2) |
+--------------------------------------+
| {"a": 2} |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_SET('{"a": 1}', "$[0].a", 2);
+-----------------------------------+
| JSON_SET('{"a": 1}', "$[0].a", 2) |
+-----------------------------------+
| {"a": 2} |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_SET('{"a": 1}', "$.a[0][1]", 2);
+--------------------------------------+
| JSON_SET('{"a": 1}', "$.a[0][1]", 2) |
+--------------------------------------+
| {"a": [1, 2]} |
+--------------------------------------+
1 row in set (0.00 sec)
```
But in Dolt, all but the first of these return incorrect results:
```sql
jtest/main*> select JSON_SET('{"a": 1}', "$.a[0]", 2);
+-----------------------------------+
| JSON_SET('{"a": 1}', "$.a[0]", 2) |
+-----------------------------------+
| {"a": 2} |
+-----------------------------------+
1 row in set (0.00 sec)
jtest/main*> select JSON_SET('{"a": 1}', "$[0].a[0]", 2);
+--------------------------------------+
| JSON_SET('{"a": 1}', "$[0].a[0]", 2) |
+--------------------------------------+
| 2 |
+--------------------------------------+
1 row in set (0.00 sec)
jtest/main*> select JSON_SET('{"a": 1}', "$[0].a", 2);
+-----------------------------------+
| JSON_SET('{"a": 1}', "$[0].a", 2) |
+-----------------------------------+
| 2 |
+-----------------------------------+
1 row in set (0.00 sec)
jtest/main*> select JSON_SET('{"a": 1}', "$.a[0][1]", 2);
+--------------------------------------+
| JSON_SET('{"a": 1}', "$.a[0][1]", 2) |
+--------------------------------------+
| {"a": 2} |
+--------------------------------------+
1 row in set (0.00 sec)
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.