如何编写SQL语句检索数据表中JSON字段内的特定数据

前置说明 #

你使用的carts表结构如下：

• crt_id：bigint(20)，主键
• crt_mbr_id：bigint(20)
• crt_session_id：varchar(128)
• crt_content：text，存储JSON数组格式数据，示例为[{"id":"24","quantity":"1","price":3000,"discounted":3000,"coupon":0}]
• crt_send_type：int(11)
• crt_completed：tinyint(1)
• created_at：timestamp
• updated_at：timestamp

需求为检索crt_content的JSON数组中包含id值为24的所有记录，不同数据库场景下的SQL写法如下： #

1. MySQL 5.7及以上版本（支持JSON函数） #

由于crt_content是text类型，需要先转换为JSON格式再使用JSON匹配函数：

SELECT * FROM carts 
WHERE JSON_CONTAINS(CAST(crt_content AS JSON), '{"id":"24"}', '$[*]');

如果后续你将crt_content字段类型修改为JSON类型，可简化写法：

SELECT * FROM carts 
WHERE JSON_CONTAINS(crt_content, '{"id":"24"}', '$[*]');

注意：如果你的业务中id存储的是数值类型而非字符串，将条件中的'{"id":"24"}'替换为'{"id":24}'即可。

2. PostgreSQL 版本 #

使用JSONB类型的包含操作符实现匹配：

SELECT * FROM carts
WHERE crt_content::jsonb @> '[{"id":"24"}]'::jsonb;

3. 不支持JSON函数的低版本数据库（应急方案） #

如果数据库版本过低无法使用JSON操作函数，可以使用模糊匹配兜底，该方案存在误判风险，仅建议临时使用：

SELECT * FROM carts
WHERE crt_content LIKE '%"id":"24"%';

模糊匹配风险提示：如果crt_content中其他位置也出现"id":"24"格式的字符串，会导致匹配到不符合要求的记录，优先使用JSON函数的方案。

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