I'm storing arrays of integers as json in a mariadb (10.3.23) table :
SELECT
tag_list
FROM
dw.final_document
LIMIT 1 ;
Result :
[903, 1258, 1261, 393]
To retrieve entries matching a specific id, this works :
SELECT SQL_NO_CACHE
count(*)
FROM
dw.final_document
where
JSON_CONTAINS(tag_list, 684 ) ;
Result :
+----------+
| count(*) |
+----------+
| 9696 |
+----------+
1 row in set (2.084 sec)
However the performance is not good without an index (2 sec in a 1M rows table)
The possibility to index a specific field of the json is well documented (percona post).
You can add an index on a generated column like this :
ALTER TABLE test_features
ADD COLUMN street VARCHAR(30)
GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) VIRTUAL;
Is there a similar way to index arrays of integers ?