[MDEV-25881] JSON_TABLE can't handle an array properly Created: 2021-06-09  Updated: 2024-02-02  Resolved: 2022-07-21

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.6.1
Fix Version/s: 10.6.9

Type: Bug Priority: Critical
Reporter: Deepak Vohra Assignee: Rucha Deodhar
Resolution: Fixed Votes: 0
Labels: JSON, JSON_TABLE
Environment:

Docker image : mariadb:10.6.1
Platform : Amazon Elastic Container Service (ECS)
OS: Amazon Linux 2

Issue Links:
Relates
relates to MDEV-25875 JSON_TABLE: extract document fragment... Closed
relates to MDEV-17399 Add support for JSON_TABLE Closed

 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.

 Comments   
Comment by Sergei Petrunia [ 2021-06-09 ]

The observed behavior is what is expected of the current code. The SQL Standard specifies that it is an error when column path points to a non-scalar value. The default "ON ERROR" behavior is to emit NULL, this is why the query produces NULLs.

MySQL's behavior is their extension. It is very reasonable though, and we are already looking at implementing it, see MDEV-25875.

Comment by Rucha Deodhar [ 2022-07-21 ] 

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;
 
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"]
 
main.temp                                [ pass ]      1

Can't repeat anymore on 10.6 commit hash : 654236c06d231461c66e2f3c5c4fd3b35cba3869

Comment by Rucha Deodhar [ 2022-07-21 ]

Seems it is fixed already. Can't repeat anymore on commit: 654236c06d231461c66e2f3c5c4fd3b35cba3869

Generated at Thu Feb 08 09:41:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.