[MDEV-30145] JSON_TABLE: allow to retrieve the key when iterating on JSON objects Created: 2022-12-02  Updated: 2023-07-25  Resolved: 2023-07-24

Status: Closed
Project: MariaDB Server
Component/s: JSON
Fix Version/s: 11.2.1

Type: Task Priority: Critical
Reporter: Christian Proust Assignee: Rucha Deodhar
Resolution: Fixed Votes: 1
Labels: JSON_TABLE, Preview_11.2

Issue Links:
Blocks
Duplicate
is duplicated by MDEV-30494 JSON_TABLE - reference to object keys... Closed
Relates
relates to MDEV-31477 Inconsistent handling while fetching ... Stalled

 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).

 Comments   
Comment by Sergei Golubchik [ 2023-02-06 ]

I suspect the standard solution would be to use keyvalue function, like (completely untested)

SELECT jt.* 
FROM JSON_TABLE(
 
    '{"a": 41, "b": 42, "c": 43}', '$.keyvalue()[*]' 
    COLUMNS (v INT(11) path '$.value', i FOR ORDINALITY, k VARCHAR(5) path '$.key')) AS jt

which is something we don't support yet

Comment by Rucha Deodhar [ 2023-05-25 ]

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).

Comment by Rucha Deodhar [ 2023-05-26 ]

Patch: https://github.com/MariaDB/server/tree/bb-11.2-MDEV-30145

Comment by Alexey Botchkov [ 2023-06-07 ]

A couple of things to fix.
See the comment to the patch.

Comment by Alexey Botchkov [ 2023-06-12 ]

still one comment to fix.
But it's ok to get this solution tested (if didn't start already).

Comment by Ramesh Sivaraman [ 2023-07-19 ]

okay to push

Generated at Thu Feb 08 10:13:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.