PostgreSQL如何查询JSON列中指定键匹配特定值的记录

根据你的列存储类型不同，对应写法如下：

标准写法（metadata为原生JSON/JSONB类型） #

PostgreSQL原生提供JSON字段操作符实现键值匹配，优先使用->>操作符提取键对应文本值做匹配，避免JSON格式自带引号导致的匹配错误，查询语句如下：

SELECT * FROM 替换为你的实际表名
WHERE metadata ->> 'state' = 'OK';

操作符说明 #

• ->>：按指定键提取JSON字段值，返回结果为去引号的文本类型，匹配字符串场景下直接写目标值即可，是最常用的写法
• ->：按指定键提取JSON字段值，返回结果仍为JSON类型，如果用该操作符匹配字符串'OK'，需要额外给匹配值加JSON字符串自带的双引号，写法为WHERE metadata -> 'state' = '"OK"'，容易写错，非必要不使用

特殊场景（metadata为文本类型存储的JSON字符串） #

如果你没有用数据库原生JSON/JSONB类型，而是用普通文本字段存JSON内容，需要先将字段显式转换为JSON类型再做匹配：

SELECT * FROM 替换为你的实际表名
WHERE metadata::jsonb ->> 'state' = 'OK';

注意：这种存储方式性能远低于原生JSON/JSONB类型，不推荐长期使用。

性能优化建议 #

如果该筛选场景查询频率很高，可以针对JSON字段的state键创建表达式索引，大幅提升查询速度：

CREATE INDEX idx_your_table_metadata_state ON 替换为你的实际表名 ((metadata ->> 'state'));

另外注意JSON键名匹配是大小写敏感的，写查询时要保证state的大小写和实际存储的键名完全一致。

内容的提问来源于stack exchange，提问作者Diego Villafañe