Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
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:
| v | i |
|---|---|
| 41 | 1 |
| 42 | 2 |
| 43 | 3 |
But there is no way to retrieve:
| v | i | k |
|---|---|---|
| 41 | 1 | a |
| 42 | 2 | b |
| 43 | 3 | c |
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).
Attachments
Issue Links
- is duplicated by
-
MDEV-30494 JSON_TABLE - reference to object keys from PATH '$.*' is not possible or documented
-
- Closed
-
- relates to
-
-
- Stalled
-
Activity
After discussing with HF, decided that implementing keyvalue() function as part of the path can become complicated. Because keyvalue() will transform an object, which means the path will "return" something instead of only leading to some character in the json. Also, we more 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 we can have the result that this function returns, as an argument in JSON_TABLE().
Something like this:
JSON_TABLE ( JSON_KEY_VALUE(<json_doc>, <path>) , PATH COLUMN ... ) .
Also this function can be used else where if needed and not restricted to path. Having what the description says ( key as a separate column, like key_list FOR KEY ) will also restrict it to json table. Let me know what you think.
Sergei approved. Because it doesn't introduce a new non-standard syntax and it is consistent with what we're doing already (There is also type() function in standards, and we have JSON_TYPE() as equivalent).
still one comment to fix.
But it's ok to get this solution tested (if didn't start already).
I suspect the standard solution would be to use keyvalue function, like (completely untested)
which is something we don't support yet