Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
MySQL 8 - Search Indexed JSON Arrays
-- DB Fiddle:
-- Create a customers table with an array JSON column containing their favourite numbers.
create table `customers` (
`id` bigint unsigned auto_increment,
`name` varchar(222),
`numbers` json not null,
primary key (`id`),
key `customers_numbers_index` ((cast(json_extract(`numbers`,_utf8mb4'$[*]') as unsigned array)))
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci;
insert into `customers` (`name`, `numbers`) values
("Joe Bloggs", "[42, 101]"),
("Jane Bloggs", "[32, 64]"),
("John Smith", "[42]"),
("Jane Smith", "[42, 99, 180]");
-- Use json_extract() and json_contains() to find an exact number in the field using the created index.
select * from `customers` where json_contains(json_extract(numbers, "$[*]"), '[42]');
explain select * from `customers` where json_contains(json_extract(numbers, "$[*]"), '[42]');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment