JSON FullText search mysql

Question

I have a test table that consists of:

CREATE TABLE `test` (
    `id`                    BINARY(16)      NOT NULL    DEFAULT (UUID_TO_BIN(UUID())),
    `name`                  VARCHAR(64)     NOT NULL,
    `phone`                 JSON            NOT NULL,
);

the thing that I want to have is have a fulltext search on phone field that is Json,

the only way that I'm reached till now is, having a generated field like below:

CREATE TABLE `test` (
    `id`                    BINARY(16)      NOT NULL    DEFAULT (UUID_TO_BIN(UUID())),
    `name`                  VARCHAR(64)     NOT NULL,
    `phone`                 JSON            NOT NULL,
    `phone_list`            VARCHAR(30)     GENERATED   ALWAYS  AS (JSON_UNQUOTE(Phone -> '$[*].Number'))   STORED,
     FULLTEXT               `Index_FullText`            (name, PhoneList)
);

is there anyway better to do this query and not having e duplicate data?

mysql version is 8.0.21

Answer 1

Could you create a multivalue index on your phone number like this:

KEY `ix_phone` ((cast(json_extract(`Phone`,_utf8mb4'$[*].Number') as char(36)

Then in your select statement use:

WHERE 'some phone number' MEMBER OF(Phone->'$[*].number')