When iterating on a JSON objects, there is currently no way to retrieve the key allowing to access to the current object.

As an example:

SELECT jt.* 

FROM JSON_TABLE(

    '{"a": 41, "b": 42, "c": 43}', '$.*' 

    COLUMNS (v INT(11) path '$', i FOR ORDINALITY)) AS jt

Will produce:

But there is no way to retrieve:

The proposal is to add, as for "FOR ORDINALITY", the "FOR KEY" clause that would allow to retrieve the key when doing:

SELECT jt.* 

FROM JSON_TABLE(

    '{"a": 41, "b": 42, "c": 43}', '$.*' 

    COLUMNS (

        v INT(11) path '$', 

        i FOR ORDINALITY,

        k VARCHAR(5) FOR KEY

    )) AS jt

Final Implementation:

Implementing keyvalue() function as part of the path can become complicated. Because keyvalue() will "transform" an object. Now, having this keyvalue() in path means the path will "return" something instead of only leading to some character in the json document. Also, there are few more "path functions"in addition to keyvalue() so supporting function in path qualifies for a completely new task.

As an alternative though, we can have a separate json function (JSON_KEY_VALUE(<json_doc>, <path>)) which transforms an object into key-value pairs and have the result that this function returns as an argument in JSON_TABLE().
Like so:
JSON_TABLE ( JSON_KEY_VALUE(<json_doc>, <path>) , PATH COLUMN ... ) .

Another reason to rather have a function:
-Can be used else where if needed and not restricted to path.

• It doesn't introduce a new non-standard syntax and it is consistent with what we're doing already ( Example there is also type() function in standards, and we have JSON_TYPE() as equivalent).