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

Add formatted column support to JSON_TABLE

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • JSON
    • None

    Description

      The SQL standard defines the following types for the column definition of a JSON_TABLE clause:

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

      All types except the formatted ones are already implemented in 10.6.0 and the only one left out is formatted columns:

      <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 ]
      		 
       
      		 
      <JSON representation> ::=
      		 
      JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ]
      		 
      | <implementation-defined JSON representation option>
      		 
      The standardized option is FORMAT JSON; implementations may also
      		 
      support syntax such as FORMAT AVRO or FORMAT BSON.

      Adding at least the minimal implementation of the FORMAT JSON clause would already help greatly when using JSON_TABLE with JSON objects with deeply nested JSON values.

      If implemented, I would expect it to behave as follows:

      SET @json='
      		 
      [
      		 
        {"name":"Jeans",  "sizes": [32, 34, 36]},
      		 
        {"name":"T-Shirt", "sizes":["Medium", "Large"]},
      		 
        {"name":"Cellphone"}
      		 
      ]';
      		 
       
      		 
      SELECT * FROM JSON_TABLE(@json, '$[*]' COLUMNS(
      		 
        name VARCHAR(10) PATH '$.name',
      		 
        sizes VARCHAR(100) FORMAT JSON PATH '$.sizes'
      		 
      )) AS jt;

      The expected result:

      +-----------+---------------------+
      		 
      | name      | sizes               |
      		 
      +-----------+---------------------+
      		 
      | Jeans     | [32, 34, 36]        |
      		 
      | T-Shirt   | ["Medium", "Large"] |
      		 
      | Cellphone | NULL                |
      		 
      +-----------+---------------------+

      Currently this can be emulated using JSON_QUERY and an ordinality column but the syntax is quite uncomfortable to use:

      SELECT jt.name, JSON_QUERY(@json, CONCAT('$[', jt.id - 1,'].sizes')) AS sizes FROM JSON_TABLE(@json, "$[*]" COLUMNS(
      		 
        id FOR ORDINALITY,
      		 
        name JSON PATH "$.name"
      		 
      )) AS jt;

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-17399 Add support for JSON_TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              Unassigned Unassigned
              markus makela markus makela
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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