Now I use SQL script like SELECT * FROM user WHERE JSON_CONTAINS(users, '[1]');But it will scan full table, it's inefficient. So I want to create the index on users column.
For example, I have a column named users, data looked like [1,2,3,4]. Please tell me how to set index on JSON array type(Generate virtual column). I had read the document on MySQL website, they all talked about to indexing in JSON object type by using JSON_EXTRACT() function.
It's now possible with MySQL 8+
Here is an example:
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON
);
ALTER TABLE customers ADD INDEX comp(id, modified,
(CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
Use it this way:
SELECT * FROM customers
-> WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
More info: https://dev.mysql.com/doc/refman/8.0/en/create-index.html
You cannot, not at least the way you intend. At The JSON Data Type we can read:
JSONcolumns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from theJSONcolumn. See Indexing a Generated Column to Provide a JSON Column Index, for a detailed example.
So with the restriction comes the workaround ;-)
