如何在PostgreSQL中查询json列存储的数组中的指定元素？

问题根源 #

你之前的查询失效有两个核心原因：

1. SQL里误用了保留字table作为表名，实际表名是example
2. content_type是json类型，不是PostgreSQL原生数组类型，无法直接使用数组的&&重叠运算符

方案1：不修改现有字段类型的查询写法 #

写法A（兼容PostgreSQL 9.4及以上版本） #

将json字段中的数组元素提取后判断是否包含目标值：

SELECT * FROM example
WHERE 'c' = ANY(ARRAY(SELECT json_array_elements_text(content_type)));

写法B（PostgreSQL 12及以上版本，json路径查询语法更简洁） #

SELECT * FROM example
WHERE content_type @? '$[*] ? (@ == "c")';

方案2：优化字段类型获得更好的查询性能 #

如果你经常需要对这个字段做数组包含查询，建议将字段类型从json改为原生的text[]数组类型，或者jsonb类型：

• 改为text[]后，你最初的写法修正表名即可正常使用：

SELECT * FROM example WHERE ARRAY['c'] && content_type;

• 改为jsonb后可以用更高效的包含运算符：

SELECT * FROM example WHERE content_type @> '"c"'::jsonb;

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