Baymine opened a new issue, #44565: URL: https://github.com/apache/doris/issues/44565
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version doris-2.1.6-jd-rc04 ### What's Wrong? When using `JSON_ARRAY` in combination with `JSON_OBJECT`, the `JSON_OBJECT` appears to be cast as a string. This behavior prevents `JSON_EXTRACT` from retrieving the desired JSON keys correctly. However, in MySQL version 5.7.19, the SQL queries operate as expected without this issue. ### What You Expected? The `JSON_EXTRACT` function should correctly retrieve the value associated with the specified JSON key without returning `<null>`. Specifically, extracting `'$[0].name'` from the JSON array should return `"Adam"`. ### How to Reproduce? 1. **Create JSON_OBJECT:** ```sql SELECT JSON_OBJECT("name", "Adam"); ``` **Output:** ``` +-------------------------------+ | json_object('name', 'Adam') | +-------------------------------+ | {"name":"Adam"} | +-------------------------------+ 1 row in set (0.015s) ``` 2. **Create JSON_ARRAY with JSON_OBJECT:** ```sql SELECT JSON_ARRAY(JSON_OBJECT("name", "Adam")); ``` **Output:** ``` +----------------------------------------------------+ | json_array(json_object('name', 'Adam'), '66') | +----------------------------------------------------+ | ["{\"name\":\"Adam\"}"] | +----------------------------------------------------+ 1 row in set ``` 3. **Attempt to Extract JSON Key:** ```sql SELECT JSON_EXTRACT(JSON_ARRAY(JSON_OBJECT("name", "Adam"), JSON_OBJECT("name", 'Kent')), '$.[0].name'); ``` **Output:** ``` +--------------------------------------------------------------------------------------------------------------------+ | json_extract(json_array(json_object('name', 'Adam'), json_object('name', 'Kent'), '66'), '$.[0].name') | +--------------------------------------------------------------------------------------------------------------------+ | <null> | +--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.015s) ``` 4. **MySQL 5.7.19 Behavior:** ```sql SELECT JSON_EXTRACT(JSON_ARRAY(JSON_OBJECT("name", "Adam"), JSON_OBJECT("name", 'Kent')), '$[0].name'); ``` **Output:** ``` +-------------------------------------------------------------------------------------------------+ | JSON_EXTRACT(JSON_ARRAY(JSON_OBJECT('name', 'Adam'), JSON_OBJECT('name', 'Kent')), '$[0].name') | +-------------------------------------------------------------------------------------------------+ | "Adam" | +-------------------------------------------------------------------------------------------------+ 1 row in set (0.004s) ``` ### Anything Else? _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
