I want to make a tags
column of type json
:
e.g.,
id | tags
=========================================
1 | '["tag1", "tag2", "tag3"]'
2 | '["tag1", "tag3", "tag5", "tag7"]'
3 | '["tag2", "tag5"]'
I want to index each tag
in the arrays, without knowing the length of the arrays (variable length).
So then if I query for rows that contain tag2
, it should return rows 1, 3.
https://dev.mysql.com/doc/refman/5.7/en/json.html
JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column
By "extracts a scalar value", does this mean I must extract & index each item in the arrays individually (meaning I must know the maximum length of the array to index them all)? How do I index a variable length array?
Note that any database will accept JSON documents as a single string blob. However, MySQL and PostgreSQL support validated JSON data in real key/value pairs rather than a basic string.
The drawback? If your JSON has multiple fields with the same key, only one of them, the last one, will be retained. The other drawback is that MySQL doesn't support indexing JSON columns, which means that searching through your JSON documents could result in a full table scan.
Contact MySQL | How large can JSON Documents be? One of the more frequently asked questions about the native JSON data type, is what size can a JSON document be. The short answer is that the maximum size is 1GB.
As the MySQL manual notes: JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column.
It's now possible with MySQL 8.0.17+
Something like this (not tested)
CREATE TABLE posts (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
tags JSON,
INDEX tags( (CAST(tags AS VARCHAR(32) ARRAY)) )
);
Use it this way:
SELECT * FROM posts
WHERE JSON_CONTAINS(tags, CAST('[tag1, tag2]' AS JSON));
More details and samples here: https://dev.mysql.com/doc/refman/8.0/en/json.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With