Json function is not compatible with Mysql

[hequn8128]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

2. What did you expect to see? (Required)

mysql version: 8.0.23

mysql> select json_extract( '[ { "a": [3,4] }, { "b": 2 } ]', '$[*].a' ) jdoc;
+----------+
| jdoc     |
+----------+
| [[3, 4]] |
+----------+
1 row in set (0.00 sec)

mysql> select json_extract( '[ { "a": 1 }, { "b": 2 } ]', '$[*].a' ) jdoc;
+------+
| jdoc |
+------+
| [1]  |
+------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT
    -> (
    -> '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
    -> '$.b[ 1 ].'
    -> );
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 9.

mysql> SELECT JSON_EXTRACT
    -> (
    -> '{ "a" : [ }',
    -> '$.b[ 1 ].c'
    -> );
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 10.

mysql> SELECT JSON_EXTRACT('[1,2,[3,4,[5,6,{"a":7}]]]', '$**[*]**[0]');
+----------------------------------------------------------+
| JSON_EXTRACT('[1,2,[3,4,[5,6,{"a":7}]]]', '$**[*]**[0]') |
+----------------------------------------------------------+
| [1, 2, 3, 4, 5, 6, {"a": 7}, 7]                          |
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[0]');
+---------------------------------------------------+
| JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[0]') |
+---------------------------------------------------+
| [[0, 1], 0, 1, 2, 3, 4, 5, 6]                     |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract( '[1]', '$**[0]' );
+---------------------------------+
| json_extract( '[1]', '$**[0]' ) |
+---------------------------------+
| [1]                             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select json_object( null, 1 );
ERROR 3158 (22032): JSON documents may not contain NULL member names.

3. What did you see instead (Required)

mysql> select json_extract( '[ { "a": [3,4] }, { "b": 2 } ]', '$[*].a' ) jdoc;
+--------+
| jdoc   |
+--------+
| [3, 4] |
+--------+
1 row in set (0.00 sec)

mysql> select json_extract( '[ { "a": 1 }, { "b": 2 } ]', '$[*].a' ) jdoc;
+------+
| jdoc |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT
    -> (
    -> '{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
    -> '$.b[ 1 ].'
    -> );
+--------------------------------------------------------------------------------+
| JSON_EXTRACT
(
'{ "a" : "foo", "b" : [ true, { "c" : 123 } ] }',
'$.b[ 1 ].'
) |
+--------------------------------------------------------------------------------+
| {"c": 123}                                                                     |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT
    -> (
    -> '{ "a" : [ }',
    -> '$.b[ 1 ].c'
    -> );
ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.

mysql> SELECT JSON_EXTRACT('[1,2,[3,4,[5,6,{"a":7}]]]', '$**[*]**[0]');
+--------------------------------------------------------------------------------------+
| JSON_EXTRACT('[1,2,[3,4,[5,6,{"a":7}]]]', '$**[*]**[0]')                             |
+--------------------------------------------------------------------------------------+
| [1, 2, 3, 3, 4, 5, 5, 6, {"a": 7}, 7, 3, 4, 5, 5, 6, {"a": 7}, 7, 5, 6, {"a": 7}, 7] |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[0]');
+---------------------------------------------------+
| JSON_EXTRACT('[[0,1],[2,3],[4,[5,6]]]', '$**[0]') |
+---------------------------------------------------+
| [[0, 1], 0, 0, 1, 2, 2, 3, 4, 4, 5, 5, 6]         |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract( '[1]', '$**[0]' );
+---------------------------------+
| json_extract( '[1]', '$**[0]' ) |
+---------------------------------+
| [1, 1]                          |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select json_object( null, 1 );
ERROR 1105 (HY000): JSON documents may not contain NULL member names

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-2047-g0aa476034
Edition: Community
Git Commit Hash: 0aa476034ae8a69040ab2b4fe455114ba0e21695
Git Branch: master
UTC Build Time: 2021-01-26 02:15:59
GoVersion: go1.15.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[lzmhhh123]

Maybe https://github.com/oliveagle/jsonpath could help us rebuild the jsonpath. TiDB's jsonpath needs to be restructure.

[morgo]

Some other test cases / this might be a duplicate of: #34959