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

JSON_TABLE can't handle an array properly

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.6.1
    • Fix Version/s: 10.6
    • Component/s: JSON
    • Labels:
    • Environment:
      Docker image : mariadb:10.6.1
      Platform : Amazon Elastic Container Service (ECS)
      OS: Amazon Linux 2

      Description

      JSON_TABLE does not process a JSON array properly for a JSON type path.

      Use case example:

      SELECT * FROM
          JSON_TABLE('[{"id": 1,"first_name": "John", "last_name": "Smith","email": ["john.smith@example.com","johnsmith@example.com"], "contact_since": 2010},
          {"id": 2,"first_name": "Jon", "last_name": "Smith","email": ["jon.smith@example.com","jonsmith@example.com"], "new_contact": true, "contact_since": 2015},
          {"id": 3,"first_name": "Johnny", "last_name": "Smith","email": ["johnny.smith@example.com","johnnysmith@example.com"], "new_contact": true, "contact_since": 2017}]',
            "$[*]"
            COLUMNS(
              rowid FOR ORDINALITY,
              email JSON PATH "$.email" DEFAULT '[]' ON EMPTY
             )
          ) AS contact;
      

      Expected Output:

      +-------+---------------------------------------------------------+
      | rowid | email                                                   |
      +-------+---------------------------------------------------------+
      |     1 | ["john.smith@example.com", "johnsmith@example.com"]     |
      |     2 | ["jon.smith@example.com", "jonsmith@example.com"]       |
      |     3 | ["johnny.smith@example.com", "johnnysmith@example.com"] |
      +-------+---------------------------------------------------------+
      

      Actual Output:

      +-------+-------+
      | rowid  | email  |
      +-------+-------+
      |     1     | NULL   |
      |     2     | NULL   |
      |     3     | NULL   |
      +-------+-------+
      

      MySQL 8 generates expected output.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              deepak Deepak Vohra
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: