如何编写SQL查询从对象数组字段中检索含指定ID的记录

你当前的查询 Select * from A where Data = 21 无法生效，因为Data列存储的是JSON格式的对象数组，不是单纯数值，需要利用对应数据库的JSON处理函数来检索数组中的对象属性。以下是主流数据库的解决方案：

MySQL 实现 #

使用JSON_CONTAINS函数直接检查数组中是否包含指定ID的对象：

SELECT * FROM A 
WHERE JSON_CONTAINS(Data, '{"id": 21}');

如果Data列是TEXT/VARCHAR类型（未标记为JSON），先转换为JSON格式再查询：

SELECT * FROM A 
WHERE JSON_CONTAINS(CAST(Data AS JSON), '{"id": 21}');

PostgreSQL 实现 #

可以通过展开JSON数组后筛选，或使用路径查询：

方式1：展开数组筛选 #

SELECT DISTINCT A.* FROM A,
jsonb_array_elements(Data::jsonb) AS elem
WHERE elem->>'id' = '21';

方式2：JSON路径查询 #

SELECT * FROM A
WHERE jsonb_path_exists(Data::jsonb, '$.id ? (@ == 21)');

SQL Server 实现 #

利用OPENJSON解析数组，关联后筛选：

SELECT DISTINCT A.* FROM A
CROSS APPLY OPENJSON(A.Data)
WITH (id INT '$.id') AS j
WHERE j.id = 21;

特殊情况处理 #

如果你的Data列存储的是非标准JSON（比如示例中的{id:21}，键名无双引号），需要先替换为标准JSON格式再查询，以MySQL为例：

SELECT * FROM A 
WHERE JSON_CONTAINS(
  REPLACE(REPLACE(Data, '{id:', '{"id":'), ', verify:', ', "verify":'), 
  '{"id": 21}'
);

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