Bug #94790 JSON - Unable to extract first element with JSON_EXTRACT
Submitted: 26 Mar 16:48
Reporter: Roberto Caiola Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:8.0.12 OS:Any
Assigned to: CPU Architecture:Any

[26 Mar 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.