MySQL Bugs: #107199: Inconsistent JSON path ** behaviour between versions

Bug #107199	Inconsistent JSON path ** behaviour between versions
Submitted:	3 May 2022 15:33	Modified:	12 May 2022 16:48
Reporter:	chen chen (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Here comes from the document:

prefix**suffix evaluates to all paths that begin with the named prefix and end with the named suffix.

https://dev.mysql.com/doc/refman/8.0/en/json.html#json-paths-last

i tried, but no success.

mysql> SELECT JSON_EXTRACT('{"acb": {"b": 1}, "c": {"b": 2}}', '$.a**b');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 5.
mysql> SELECT JSON_EXTRACT('{"acb": {"b": 1}, "c": {"b": 2}}', '$."a**b"');
+--------------------------------------------------------------+
| JSON_EXTRACT('{"acb": {"b": 1}, "c": {"b": 2}}', '$."a**b"') |
+--------------------------------------------------------------+
| NULL                                                         |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

Can you give an example in document?

How to repeat:
as above

Hello Chen,

Thank you for the documentation enhancement request!

Thanks,
Umesh

Hi Chen,

'**' matches any sequence of keys. It is not a wildcard that matches partial key names; the exact key names used as the prefix and the suffix must be present in the JSON in order to be matched.

For example, '$.a**.c' matches .a.b.c, .a.bx.c, a.e.g.c., etc 
but not ax.b.c, .a.b.cx, .a.b.c.d, etc.

In the course of testing some examples, I found that MySQL 5.7 and 8.0 give different results for some uses of '**' and that both give unexpected results in some cases, so I'm turning this into a software bug.

We'll revisit the issue with the documentation once the software issues have been resolved.

Thanks!

5.7.38:

mysql> SELECT JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.ab**.jkl');
+------------------------------------------------------------------------------------------------------------+
| JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.ab**.jkl') |
+------------------------------------------------------------------------------------------------------------+
| NULL                                                                                                       |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.abc**.jkl');
+-------------------------------------------------------------------------------------------------------------+
| JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.abc**.jkl') |
+-------------------------------------------------------------------------------------------------------------+
| [4]                                                                                                         |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+-------------------------------------------------------------------------------------------+

8.0.30:

mysql> SELECT JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.ab**.jkl');
+------------------------------------------------------------------------------------------------------------+
| JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.ab**.jkl') |
+------------------------------------------------------------------------------------------------------------+
| NULL                                                                                                       |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.abc**.jkl');
+-------------------------------------------------------------------------------------------------------------+
| JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.abc**.jkl') |
+-------------------------------------------------------------------------------------------------------------+
| [8]                                                                                                         |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The result of the first query is correct--MySQL thinks you're looking for a match on a path beginning with a key named 'ab' which does not match any of the keys in the document. But shouldn't this 

JSON_EXTRACT('{"abc": {"def": {"ghi": 3, "jkl": 4}}, "abc": {"pqr": {"mno": 7, "jkl": 8}}}', '$.abc**.jkl') 

return this 

[4, 8]

in both cases? 

It also seems odd that 5.7 matches only the one, yet 8.0 matches only the other.

Thanks!