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

JSON_TABLE can't handle an array properly

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.6.1
    • 10.6.9
    • JSON
    • 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

              rucha174 Rucha Deodhar
              dvohra Deepak Vohra
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.