如何通过PostgreSQL查询实现多语言过滤并统一返回格式

原查询不可行 #

你写的查询语句存在两个核心问题：

• 完全依赖数组的固定索引位置，只有当原数据里language数组第1个元素是Hindi、第2个是English时才会命中，要是原数组顺序反过来（比如["English", "Hindi"]）就查不到了
• 没处理原数组包含额外语言的情况，比如原数组是["Hindi", "English", "Punjabi"]，你的查询会直接漏掉这类符合条件的行

正确的PostgreSQL查询语句 #

假设你的language字段是JSON类型（如果是JSONB类型，把语法里的::json换成::jsonb即可），以下是满足需求的查询：

方式1：保留原字段，同时返回统一格式的language

SELECT
  -- 保留你需要的其他字段，比如id、title等，*代表所有字段，可根据实际替换
  *,
  -- 构造统一格式的返回结构
  json_build_object('language', '["English", "Hindi"]'::json) AS formatted_language
FROM tbl_lang
WHERE
  -- 筛选原数组包含所有指定语言的行（无视顺序、无视额外语言）
  '["Hindi", "English"]'::jsonb <@ language::jsonb

方式2：直接替换原language字段为统一格式

SELECT
  -- 替换成你需要的其他字段，比如id, content, ...
  id,
  json_build_object('language', '["English", "Hindi"]'::json) AS language
FROM tbl_lang
WHERE
  '["Hindi", "English"]'::jsonb <@ language::jsonb

关键语法说明 #

• <@是PostgreSQL JSONB的子集判断操作符，A <@ B表示数组A的所有元素都存在于数组B中，刚好满足"原数据包含所有指定语言"的筛选需求
• json_build_object用来直接生成你需要的统一JSON结构，彻底规避原数据的顺序和额外语言问题

内容的提问来源于stack exchange，提问作者suraj karosia