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

Add formatted column support to JSON_TABLE

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: JSON
    • Labels:
      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

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              markus makela markus makela
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: