Bug #99675 JSON array index not used with JSON_CONTAINS() and path parameter

• Submitted: 23 May 2020 21:02
• Modified: 26 May 2020 12:21
• Reporter: Jonas Staudenmeir
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S4 (Feature request)
• Version: 8.0.20
• OS: Any
• CPU Architecture: Any

[23 May 2020 21:02] Jonas Staudenmeir

Description:
The documentation shows an example for optimizing JSON_CONTAINS() queries with a multi-valued index: 
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

The example works correctly, but the index is not being used on the equivalent query that splits the first argument of JSON_CONTAINS() and provides the path as the third argument.

How to repeat:
CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON,
    INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
);

INSERT INTO customers VALUES
    (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');

# Uses index
EXPLAIN SELECT * FROM customers 
WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

# Does not use index
EXPLAIN SELECT * FROM customers 
WHERE JSON_CONTAINS(custinfo, CAST('[94507,94582]' AS JSON), '$.zipcode');

[25 May 2020 12:11] MySQL Verification Team

Hi Mr. Staudenmeir,

Thank a lot for the documentation bug info.

First of all, have you tried your second example with many more rows in the table ???

Second, would you opt for the documentation bug or for feature request ????

Thanks in advance.

[26 May 2020 12:21] MySQL Verification Team

Hi Mr. Staudenmeir,

Thank you for confirming it.

This is a useful feature request.

Verified as a feature request.