dolthub / dolthub/dolt

Support for array and multi-value index (JSON)

Open
#9,249 4 comments 0 reactions 0 assignees View on GitHub
customer issue enhancement indexes
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

MySQL 8.0.17 supports multi-valued indices by casting JSON arrays to an unsigned array, which would be very handy for indexing JSON.

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

Currently, this example from the link above...
```sql
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON
);

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]}');

ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
```

...returns this error:
```
Query 1 ERROR at Line 17: : syntax error at position 44 near 'zips'
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.