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

JSON_TABLE: extract document fragment into JSON column

Details

    • Task
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 10.6.9
    • JSON
    • None

    Description

      Currently, JSON_TABLE doesn't allow one to extract a JSON "subdocument" into a JSON column.

      Both the SQL Standard and MySQL-8 provide this capability. This task is about implementing it.

      I was sent a patch with MySQL-8 syntax support, so I assume we're going to implement MySQL-8 compatible syntax, which is just to allow specifying column type as JSON. For example:

      select * from
        json_table('{"foo": [1,2,3,4]}',
                   '$' columns( jscol json path '$.foo')
                   ) as T;
      

      should emit [1,2,3,4].

      Attachments

        Issue Links

          Activity

            psergei Sergei Petrunia added a comment - First patch: https://github.com/MariaDB/server/commit/02469bdead5753eccb5d70c98a158a07027f4eb2
            psergei Sergei Petrunia added a comment - Review input: https://lists.launchpad.net/maria-developers/msg12761.html

            For the record, the SQL Standard's way of extracting JSON documents is as
            follows:

            Columns can be either "regular" (this is what is currently supported), or
            "formatted":

            <JSON table column definition> ::=
                <JSON table ordinality column definition>
              | <JSON table regular column definition>
              | <JSON table formatted column definition>
              | <JSON table nested columns>
            

            Regular columns are just name-datatype:

            <JSON table regular column definition> ::=
              <column name> <data type>
                [ PATH <JSON table column path specification> ]
                ...
            

            Formatted columns have more options:

            <JSON table formatted column definition> ::=
              <column name> <data type>
                FORMAT <JSON representation>
                [ PATH <JSON table column path specification> ]
                [ <JSON table formatted column wrapper behavior> WRAPPER ]
                [ <JSON table formatted column quotes behavior> QUOTES [ ON SCALAR STRING ] ]
                [ <JSON table formatted column empty behavior> ON EMPTY ]
                [ <JSON table formatted column error behavior> ON ERROR ]
            

            one can produce an empty array or empty object in on-error and on-empty conditions, etc.

            psergei Sergei Petrunia added a comment - For the record, the SQL Standard's way of extracting JSON documents is as follows: Columns can be either "regular" (this is what is currently supported), or "formatted": <JSON table column definition> ::= <JSON table ordinality column definition> | <JSON table regular column definition> | <JSON table formatted column definition> | <JSON table nested columns> Regular columns are just name-datatype: <JSON table regular column definition> ::= <column name> <data type> [ PATH <JSON table column path specification> ] ... Formatted columns have more options: <JSON table formatted column definition> ::= <column name> <data type> FORMAT <JSON representation> [ PATH <JSON table column path specification> ] [ <JSON table formatted column wrapper behavior> WRAPPER ] [ <JSON table formatted column quotes behavior> QUOTES [ ON SCALAR STRING ] ] [ <JSON table formatted column empty behavior> ON EMPTY ] [ <JSON table formatted column error behavior> ON ERROR ] one can produce an empty array or empty object in on-error and on-empty conditions, etc.
            holyfoot Alexey Botchkov added a comment - https://github.com/MariaDB/server/commit/fe0dc6ba769dcb468b37a8c3e3c636c0049f7307

            Review input:

            --- a/mysql-test/suite/json/t/json_table.test
            +++ b/mysql-test/suite/json/t/json_table.test
            ...
            +#
            +# MDEV-25875 SON_TABLE: extract document fragment into JSON column.
            +#
            

            Please fix the typo, SON_TABLE.

            --- a/sql/json_table.cc
            +++ b/sql/json_table.cc
             
            +  if (ctype == PATH)
            +    m_format_json=
            +      MY_TEST(m_field->type_handler() == &type_handler_json_longtext);
            +
            

            Please use { } brackets as the statement inside the if takes multiple lines.
            I've had to change the constant to type_handler_long_blob_json when applying the patch.

            Ok to push after the above is addressed.

            psergei Sergei Petrunia added a comment - Review input: --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test ... +# +# MDEV-25875 SON_TABLE: extract document fragment into JSON column. +# Please fix the typo, SON_TABLE. --- a/sql/json_table.cc +++ b/sql/json_table.cc   + if (ctype == PATH) + m_format_json= + MY_TEST(m_field->type_handler() == &type_handler_json_longtext); + Please use { } brackets as the statement inside the if takes multiple lines. I've had to change the constant to type_handler_long_blob_json when applying the patch. Ok to push after the above is addressed.
            rucha174 Rucha Deodhar added a comment -

            Patch for community server: Patch1 and fixup for Patch 1

            rucha174 Rucha Deodhar added a comment - Patch for community server: Patch1 and fixup for Patch 1

            Hi Rucha,

            Please change the commit title (of both commits) to mention
            MDEV-25875: JSON_TABLE: extract document fragment into JSON column

            ok to push after that is done.

            psergei Sergei Petrunia added a comment - Hi Rucha, Please change the commit title (of both commits) to mention MDEV-25875 : JSON_TABLE: extract document fragment into JSON column ok to push after that is done.
            rucha174 Rucha Deodhar added a comment -

            Pushed to 10.6 community server: a9f6abe and a616035

            rucha174 Rucha Deodhar added a comment - Pushed to 10.6 community server: a9f6abe and a616035

            People

              rucha174 Rucha Deodhar
              psergei Sergei Petrunia
              Votes:
              1 Vote for this issue
              Watchers:
              11 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.