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

Add formatted column support to JSON_TABLE



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


      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"]},
      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;


        Issue Links



              Unassigned Unassigned
              markus makela markus makela
              0 Vote for this issue
              3 Start watching this issue



                Git Integration

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