MySql JSON Search Array

Search with JSON in a case insensitive manner across array values

Create a table like so

create table test.users
(
    id int auto_increment primary key,
    identifiers json null
);

Fill the table with some sample data

insert into users (identifiers)
values ('{"names": ["Johnny", "John", "Jay"], "id": "12345z" }'),
    ('{"names": ["Jennifer", "Jenny", "Jen"], "id": "a45900" }'),
    ('{"names": ["Richard", "Rich", "Ricky"], "id": "1tg22" }'),
    ('{"names": ["Erica", "Frederica", "Reddie"], "id": "pao2" }'),
    ('{"names": ["Frederick", "Freddie", "Fred"], "id": "zapz1" }')

The Identifiers column is a JSON column type and I now want to search across the various names a user could have stored in a case insensitive manner so first I need to use the JSON_SEARCH function with the all matching to keep searching across the inputs

Example query: I want to find the users with names like red

select *
from users
where JSON_SEARCH(LOWER(identifiers->'$.names'), 'all', LOWER('%red%'))

Note that to get case insensitivity matching have to UPPER or LOWER the search input and the search parameter to be sure they are in the same alignment