MySQL：实现动态表单中JSON元素精准匹配的方法

dingcho

于 2024-08-24 00:53:27 发布

阅读量18

点赞数 1

分类专栏：存储数据文章标签： mysql json

本文链接：https://blog.csdn.net/yao_guai/article/details/141477138

版权

存储数据专栏收录该内容

54 篇文章 0 订阅

订阅专栏

-- 创建表
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"}', '$')