CODEWITHSUNDEEP
mysqljson
Smith Cruise
Smith Cruise

Reputation: 444

How to create index on json array type in mysql5.7

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.

Upvotes: 1

Views: 3055

Answers (2)

hjahan
hjahan

Reputation: 378

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

Upvotes: 2

Álvaro González
Álvaro González

Reputation: 146460

You cannot, not at least the way you intend. At The JSON Data Type we can read:

JSON columns, 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 the JSON column. See Indexing a Generated Column to Provide a JSON Column Index, for a detailed example.

So with the restriction comes the workaround ;-)

Upvotes: 1

Related Questions