PostgreSQL中筛选仅含选定食材的食谱行的实现方法

针对你用PERN栈开发食谱应用时遇到的筛选问题，这里提供基于PostgreSQL JSONB数组操作的具体方案，适配你需要的两种筛选逻辑：

核心前提 #

假设你的recipes表中ner列是JSONB类型（如果当前是JSON类型，可通过ner::jsonb转换），存储格式为食材字符串数组，比如["chicken", "butter", "onion"]。

1. 筛选「仅使用选定食材（无额外食材）」的食谱 #

这是你示例中需要的逻辑：食谱的所有食材都来自用户选定的集合，不管用了其中几个（比如用户选4种食材，返回用了2种、3种或全部4种的食谱，只要没有额外食材）。

方法一：用NOT EXISTS子查询 #

SELECT *
FROM recipes
WHERE NOT EXISTS (
  -- 检查食谱中是否存在不在选定集合里的食材
  SELECT 1
  FROM jsonb_array_elements_text(ner) AS ingredient
  WHERE ingredient NOT IN ('chicken', 'butter', 'onion', 'garlic')
);

方法二：用数组子集操作符<@ #

先把JSONB数组转成PostgreSQL原生数组，再判断是否为选定数组的子集：

SELECT *
FROM recipes
WHERE (
  SELECT array_agg(ingredient) 
  FROM jsonb_array_elements_text(ner) AS ingredient
) <@ ARRAY['chicken', 'butter', 'onion', 'garlic'];

2. 筛选「恰好包含所有选定食材」的食谱 #

如果需要严格匹配食谱食材和用户选定的完全一致（不多不少），可以用以下查询：

SELECT *
FROM recipes
WHERE (
  -- 确保所有选定食材都在食谱中，且食谱食材数量和选定数量一致
  (SELECT array_agg(DISTINCT ingredient) FROM jsonb_array_elements_text(ner) AS ingredient) @> ARRAY['chicken', 'butter', 'onion', 'garlic']
  AND jsonb_array_length(ner) = 4
);

Node.js集成示例（参数化查询） #

在Node.js中使用参数化查询避免SQL注入，同时动态传入用户选定的食材：

const { Pool } = require('pg');
const pool = new Pool({ /* 你的数据库配置 */ });

async function getRecipesByIngredients(selectedIngredients) {
  // 筛选仅使用选定食材的食谱
  const query = `
    SELECT *
    FROM recipes
    WHERE NOT EXISTS (
      SELECT 1
      FROM jsonb_array_elements_text(ner) AS ingredient
      WHERE ingredient != ANY($1)
    );
  `;
  
  const result = await pool.query(query, [selectedIngredients]);
  return result.rows;
}

// 使用示例
const userSelectedIngredients = ['chicken', 'butter', 'onion', 'garlic'];
getRecipesByIngredients(userSelectedIngredients)
  .then(recipes => console.log(recipes))
  .catch(err => console.error(err));

性能优化建议 #

• 将ner列改为jsonb类型：比JSON类型支持更多操作符，查询性能更优。
• 创建GIN索引：针对ner列创建索引，大幅提升JSONB数组查询速度：

  CREATE INDEX idx_recipes_ner ON recipes USING GIN (ner);
  

• 统一大小写：如果存在食材大小写不一致的情况，可统一转小写后比较，避免漏查：

  SELECT *
  FROM recipes
  WHERE NOT EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(ner) AS ingredient
    WHERE LOWER(ingredient) NOT IN (LOWER('chicken'), LOWER('butter'), LOWER('onion'), LOWER('garlic'))
  );
  

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