PostgreSQL查询：筛选JSON数组含pdf类型文档的行

我有一张名为uploads_table的表，结构及数据如下：

需要编写PostgreSQL查询语句，返回表中至少包含一个doc_type为pdf的upload_id和value字段，预期结果如下：

方法1：展开JSON数组筛选 #

通过json_array_elements拆分JSON数组，筛选符合条件的记录后去重：

SELECT DISTINCT upload_id, value
FROM uploads_table
JOIN json_array_elements(value::json) AS docs ON docs->>'doc_type' = 'pdf';

方法2：直接检查JSON数组（性能更优） #

利用jsonb_exists_any函数直接判断数组中是否存在目标对象，无需展开数组：

SELECT upload_id, value
FROM uploads_table
WHERE jsonb_exists_any(value::jsonb, ARRAY['{"doc_type": "pdf"}']::jsonb[]);

说明 #

• 方法1适合需要对数组元素做额外处理的场景，DISTINCT用于确保每个upload_id仅返回一次；
• 方法2无需拆分数组，处理大数据量时效率更高。若value字段本身为jsonb类型，可移除::jsonb转换。

内容的提问来源于stack exchange，提问作者Kaushik J