How to INDEX JSON ARRAY stored in own column

Question

i have this table:

CREATE TABLE `datacollector` (
  `id` binary(16) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N',
  `processed_at` timestamp NULL DEFAULT NULL,
  `request` json NOT NULL,
  `response` json NOT NULL,
  `response_date` timestamp GENERATED ALWAYS AS (from_unixtime(json_unquote(json_extract(`response`,_utf8mb4'$.date')))) VIRTUAL NULL,
  `sha224` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `available_for` json NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_sha224` (`sha224`),
  KEY `created_at` (`created_at`) /*!80000 INVISIBLE */,
  KEY `cmoa` (`created_at`,`processed`) /*!80000 INVISIBLE */,
  KEY `response_date` (`response_date`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I use a trigger that creates an array of strings in available_for. Now i would like to create a Index for available_for.

I tried already

ALTER TABLE `hwtools`.`datacollector` 
ADD INDEX `available_for_idx` ((CAST(json_unquote(available_for) AS UNSIGNED ARRAY)));

or

ALTER TABLE `hwtools`.`datacollector` 
ADD INDEX `available_for_idx` ((CAST(available_for AS UNSIGNED ARRAY)));

which give me always an error.

The JSON that is stored in available_for looks like '["getUserInfo", "stashClient"]'.

How can i create now with the last MySQL 8 a correct index?

Thank you for your Input on my problem.

What is the exact error message? The json values seem to be strings, not unsigned integers. Why do you use unsigned as the array type? — Shadow
– Shadow, Commented Jul 11, 2020 at 11:50
Beacuse it was not able to use Varchar... 13:21:41 ALTER TABLE hwtools.datacollector ADD INDEX available_for_idx ((CAST(available_for AS VARCHAR(32) ARRAY))) VISIBLE Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(32) ARRAY))) VISIBLE' at line 2 0.031 sec — Jomaar
– Jomaar, Commented Jul 11, 2020 at 12:36
Also 14:39:00 ALTER TABLE hwtools.datacollector ADD INDEX available_for_idx ((CAST(available_for AS CHAR ARRAY))) VISIBLE Error Code: 1235. This version of MySQL doesn't yet support 'CAST-ing data to array of char/binary BLOBs' 0.031 sec — Jomaar
– Jomaar, Commented Jul 11, 2020 at 12:39
You should use char, not varchar, but the 2nd error indicates that your version may not support this feature yet. — Shadow
– Shadow, Commented Jul 11, 2020 at 12:49
What do you want an index for on a JSON column? Indexing keys makes sense, but indexing documents doesn't. Please include the query you want to optimize. — The Impaler
– The Impaler, Commented Jul 11, 2020 at 13:05

The Impaler · Accepted Answer · 2021-06-10 11:46:45Z

5

Multi-Valued Indexes are only supported since MySQL 8.0.17 using InnoDB engine.

You also need to watch what type you cast the array of. An unsigned array is an array of unsigned integers. For an array of strings, use char(255) array or similar.

This DDL should work just fine:

CREATE TABLE `datacollector` (
  `id` binary(16) NOT NULL,
  `available_for` json NOT NULL,
  PRIMARY KEY (`id`),
  KEY available_for_idx ((CAST(available_for AS char(255) ARRAY)))
) ENGINE=InnoDB;

Edit: I wanted to add to the explanation. Unlike traditional indexes where there's a single index node per table row, the manual explains:

multi-valued indexes can have multiple index records for a[/every] single data record (N:1).

That was news to me. You learn something new every day.

edited Jun 10, 2021 at 11:46

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

answered Jul 11, 2020 at 12:46

Δ O

3,7382 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jomaar Over a year ago

Thank you, i missed the char(255) thats why it not was working.

The Impaler Over a year ago

What kind of search do you expect this index will be good for? Apart from strict/identity equality I don't see much use for it.

Δ O Over a year ago

@TheImpaler, I think it optimises search even for an single member of the row's indexed array. See dev.mysql.com/doc/refman/8.0/en/…

The Impaler Over a year ago

Interesting. . .

Marcus Over a year ago

@TheImpaler the reason is that MySQL doesn't directly support arrays storage and indexing. For this reason, a strategy to store arrays is to store them as JSON arrays (documents), and add an MVI for that column.

Collectives™ on Stack Overflow

How to INDEX JSON ARRAY stored in own column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related