如何从MariaDB的TEXT类型JSON字段中检索指定Key的记录？

需要编写SQL查询语句，从存储JSON格式文本的post_sender_detail表中，获取tags字段包含指定键（例如键31）的匹配数据。

表结构 #

CREATE TABLE `post_sender_detail` (
  `version_id` int(10) unsigned NOT NULL,
  `subject` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tags` text DEFAULT NULL COMMENT 'JSON of tags details',
  PRIMARY KEY (`version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

现有表数据 #

期望查询逻辑与结果 #

期望实现类似如下逻辑的查询：

select * from post_sender_detail where tags match the key as 31;

查询后预期结果：

数据库版本信息 #

可行查询方案 #

你的MariaDB 10.4.7版本支持完整的JSON函数，可以通过以下两种方式实现需求：

方法一：使用JSON_CONTAINS_PATH函数 #

该函数用于判断JSON文档是否包含指定路径，直接验证目标键是否存在：

SELECT * FROM post_sender_detail
WHERE tags IS NOT NULL
AND JSON_CONTAINS_PATH(tags, 'one', '$.31');

• 'one'表示只要存在任意一个指定路径即匹配
• $.31是JSON路径，对应tags中的键31

方法二：使用JSON_EXTRACT函数 #

通过提取目标键对应的值，判断其是否非空来验证键的存在：

SELECT * FROM post_sender_detail
WHERE tags IS NOT NULL
AND JSON_EXTRACT(tags, '$.31') IS NOT NULL;

两种方法都可以排除tags为NULL的记录，精准匹配到包含指定键的数据。

内容的提问来源于stack exchange，提问作者rkrathor