Bug #94790 JSON - Unable to extract first element with JSON_EXTRACT

• Submitted: 26 Mar 2019 16:48
• Modified: 9 Apr 2020 13:41
• Reporter: Roberto Caiola
• Status: Not a Bug
• Category: MySQL Server: JSON
• Severity: S4 (Feature request)
• Version: 8.0.12
• OS: Any
• CPU Architecture: Any

[26 Mar 2019 16:48] Roberto Caiola

Description:
Unable to extract first element from JSON with JSON_EXTRACT

How to repeat:
It returns a list of one element:

SELECT JSON_EXTRACT('{"P8010":{"R000000":{"R00000030":"123"}}}', '$**.R00000030[0]') AS id;

SELECT JSON_EXTRACT('{"P8010":{"R000000":{"R00000030":"123"}}}', '$.*.*.R00000030[0]') AS id;

The work-around is to use JSON_EXTRACT twice:

SELECT JSON_EXTRACT(JSON_EXTRACT('{"P8010":{"R000000":{"R00000030":"123"}}}', '$.*.*.R00000030'), '$[0]') AS id;

This was we can JSON_UNQUOTE properly:

SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT('{"P8010":{"R000000":{"R00000030":"123"}}}', '$.*.*.R00000030'),'$[0]')) AS id;

Suggested fix:
Be able to extract the first element from the JSON.

[9 Apr 2020 13:41] MySQL Verification Team

Hi Mr. Caiola,

Thank you for your feature request.

However, the release that you are using is too old. Current 8.0 releases have a correct JSON syntax, which is why all your provided examples return a syntax error. The following one:

ERROR 3141 (22032) at line 1: Invalid JSON text in argument 1 to function json_extract: "Missing a name for object member." at position 1.

Hence, this is not acceptable feature request.