JSON_EXTRACT function does not get the expected result

[Kongdom]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

SELECT JSON_EXTRACT('{"properties": {"$type": "TiDB"}}','$.properties.$type')

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

"TiDB"

3. What did you see instead (Required)

{"$type": "TiDB"}

4. What is your TiDB version? (Required)

5.7.25-TiDB-v5.1.0

[Kongdom]

When using the JSON_EXTRACT function, if the name of the json field contains the $ symbol, you will not get the expected result.
For example, the following statement outputs valid results in MySQL and invalid results in TiDB.

SELECT JSON_EXTRACT('{"properties": {"$type": "TiDB"}}','$.properties.$type')

However, it is valid in TiDB to write the following instead, just adding the quotes.

SELECT JSON_EXTRACT('{"properties": {"$type": "TiDB"}}','$.properties."$type"')

Although, the expected result can be obtained by the above writing, I still hope that TiDB will be compatible with the initial writing and reduce the syntax difference between TiDB and MySQL.

Finally, technology connects you and me, code creates the future!

[znhh6018]

/assign

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.