Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30145

JSON_TABLE: allow to retrieve the key when iterating on JSON objects

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30494 JSON_TABLE - reference to object keys from PATH '$.*' is not possible or documented

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31477 Inconsistent handling while fetching values in json

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            proust Christian Proust created issue -
            alice Alice Sherepa made changes -
            Field Original Value New Value
            serg Sergei Golubchik added a comment -

            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

            serg Sergei Golubchik added a comment - 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
            serg Sergei Golubchik made changes -
            Assignee Rucha Deodhar [ rucha174 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.2 [ 28603 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            rucha174 Rucha Deodhar made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            rucha174 Rucha Deodhar added a comment -

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

            rucha174 Rucha Deodhar added a comment - 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).
            rucha174 Rucha Deodhar added a comment -

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

            rucha174 Rucha Deodhar added a comment - Patch: https://github.com/MariaDB/server/tree/bb-11.2-MDEV-30145
            rucha174 Rucha Deodhar made changes -
            Assignee Rucha Deodhar [ rucha174 ] Alexey Botchkov [ holyfoot ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            holyfoot Alexey Botchkov added a comment -

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

            holyfoot Alexey Botchkov added a comment - A couple of things to fix. See the comment to the patch.
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Rucha Deodhar [ rucha174 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            rucha174 Rucha Deodhar made changes -
            Assignee Rucha Deodhar [ rucha174 ] Alexey Botchkov [ holyfoot ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            holyfoot Alexey Botchkov added a comment -

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

            holyfoot Alexey Botchkov added a comment - still one comment to fix. But it's ok to get this solution tested (if didn't start already).
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Rucha Deodhar [ rucha174 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            rucha174 Rucha Deodhar made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            rucha174 Rucha Deodhar made changes -
            Assignee Rucha Deodhar [ rucha174 ] Ramesh Sivaraman [ JIRAUSER48189 ]
            ramesh Ramesh Sivaraman made changes -
            ramesh Ramesh Sivaraman made changes -
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels JSON_TABLE JSON_TABLE Preview_11.2
            ramesh Ramesh Sivaraman added a comment -

            okay to push

            ramesh Ramesh Sivaraman added a comment - okay to push
            ramesh Ramesh Sivaraman made changes -
            Assignee Ramesh Sivaraman [ JIRAUSER48189 ] Rucha Deodhar [ rucha174 ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            rucha174 Rucha Deodhar made changes -
            Fix Version/s 11.2.1 [ 29034 ]
            Fix Version/s 11.2 [ 28603 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            rucha174 Rucha Deodhar made changes -
            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:
            {code:sql}
            SELECT jt.*
            FROM JSON_TABLE(
                '{"a": 41, "b": 42, "c": 43}', '$.*'
                COLUMNS (v INT(11) path '$', i FOR ORDINALITY)) AS jt
            {code}

            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:

            {code:sql}
            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
            {code}             		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:
            {code:sql}
            SELECT jt.*
            FROM JSON_TABLE(
                '{"a": 41, "b": 42, "c": 43}', '$.*'
                COLUMNS (v INT(11) path '$', i FOR ORDINALITY)) AS jt
            {code}

            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:

            {code:sql}
            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
            {code}

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

            People

              rucha174 Rucha Deodhar
              proust Christian Proust
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.