-- 创建表
CREATE TABLE `tst_json` (
`f_id` INT NOT NULL AUTO_INCREMENT,
`f_arrays` JSON NULL,
`f_object` JSON NULL,
PRIMARY KEY (`f_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- 插入模拟数据
insert into tst_json (f_arrays, f_object) values
(JSON_ARRAY("1", "2", "3", "4"), JSON_OBJECT("name", "Anna", "age", 16)),
(JSON_ARRAY("2", "433", "5", "6"), JSON_OBJECT("name", "Bob", "age", 17)),
(JSON_ARRAY("9", "61"), JSON_OBJECT("name", "Candy", "age", 18)),
(JSON_ARRAY("11", "22", "33", 'Elden "Ldord"'), JSON_OBJECT("name", "davied", "age", 20)),
(JSON_ARRAY("99"), JSON_OBJECT("name", "Edric", "age", 19));
select * from tst_json where JSON_EXTRACT(f_arrays, '$[0]') = '1';
select * from tst_json where f_object -> '$.age' > 18;
select * from tst_json where f_arrays -> '$[1]' = '22';
select * from tst_json where JSON_CONTAINS(f_object, '{"color": "red"}', '$')