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

[nicktobey]

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:

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:

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.