PostgreSQL中如何查询games表JSON数组包含指定玩家的行？

我有一张名为games的表，包含两个字段：

• name（varchar类型）
• data（json类型）

示例数据如下：

我需要查询包含名为PlayerOne的玩家的行，尝试了以下SQL语句但未成功：

SELECT * FROM games WHERE data -> players = 'PlayerOne';
SELECT * FROM games WHERE data ->> players = 'PlayerOne';

你的SQL语句存在两个问题：

1. 访问JSON键名时，必须用单引号包裹，比如'players'，不能直接写players
2. 直接将JSON数组与字符串做相等比较是无效的，需要用数据库提供的JSON数组包含判断方法

PostgreSQL 环境下的解决方法 #

方法1：使用@>操作符（推荐，性能更优）

这个操作符可以直接判断JSON数据是否包含指定的结构：

SELECT * FROM games WHERE data @> '{"players": ["PlayerOne"]}';

方法2：展开数组后过滤

通过json_array_elements函数将数组元素展开，再匹配目标值：

SELECT DISTINCT g.* 
FROM games g, json_array_elements(data->'players') AS player
WHERE player->>'$' = 'PlayerOne';

MySQL 环境下的解决方法 #

使用JSON_CONTAINS函数判断数组是否包含目标元素，注意第二个参数需要是带双引号的JSON格式字符串：

SELECT * FROM games WHERE JSON_CONTAINS(data->'$.players', '"PlayerOne"');

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