PostgreSQL11如何按同结构jsonb数组字段筛选参会人数据？

核心实现逻辑 #

你原来的写法将每个过滤条件拆为OR判断，只能匹配任意一个条件，要实现所有条件同时满足，只需在原有SQL基础上增加按参会人分组统计匹配的过滤字段数量的逻辑，要求匹配数量等于查询过滤器的总长度即可。
因为只有参会人同时满足了所有查询字段的规则，统计出的匹配字段数才会和查询过滤器的数量完全相等。

修改后SQL示例（对应你给出的2个查询条件场景） #

SELECT attendee.id,
       attendee.email,
       attendee.eventfilters
FROM attendee
CROSS JOIN LATERAL jsonb_array_elements(attendee.eventfilters) single_filter
WHERE 
    ((single_filter ->> 'field') = 'Org Type' AND (single_filter -> 'selected') ?| array ['B2C', 'Nonprofit'])
    OR ((single_filter ->> 'field') = 'Industry Sector' AND (single_filter -> 'selected') ?| array ['Advertising'])
GROUP BY attendee.id, attendee.email, attendee.eventfilters
-- 匹配数量等于查询过滤器的总长度（这里示例是2个查询条件，所以写2）
HAVING COUNT(DISTINCT single_filter ->> 'field') = 2;

注意：这里把(single_filter ->> 'selected')::jsonb简化为single_filter -> 'selected'，因为直接取jsonb类型的selected字段不需要额外转类型，性能更好。

动态生成逻辑修改（JS代码） #

只需要在原有生成WHERE条件的基础上，额外拼接HAVING子句即可：

function buildEventFiltersQuery(eventFilters) {
  // 无查询条件时直接返回全量查询
  if (!eventFilters || eventFilters.length === 0) {
    return `SELECT id, email, eventfilters FROM attendee`;
  }
  // 生成WHERE部分的OR条件
  const whereConditions = eventFilters.map((filter) => {
    // 这里转义单引号避免SQL注入，生产环境建议用参数化查询代替字符串拼接
    const escapedField = filter.field.replace(/'/g, "''");
    const selectedArray = filter.selected.map((s) => `'${s.replace(/'/g, "''")}'`).join(', ');
    return `((single_filter ->> 'field') = '${escapedField}' AND (single_filter -> 'selected') ?| array[${selectedArray}])`;
  }).join('\nOR ');
  // 完整SQL
  return `
SELECT attendee.id,
       attendee.email,
       attendee.eventfilters
FROM attendee
CROSS JOIN LATERAL jsonb_array_elements(attendee.eventfilters) single_filter
WHERE ${whereConditions}
GROUP BY attendee.id, attendee.email, attendee.eventfilters
HAVING COUNT(DISTINCT single_filter ->> 'field') = ${eventFilters.length};
  `;
}

边界情况处理 #

• 如果查询过滤器为空数组，直接返回所有参会人即可
• 如果参会人eventfilters字段存在重复的field配置，使用COUNT(DISTINCT)可以避免重复计数导致的判断错误
• 该方案完全兼容PostgreSQL 11版本，不需要用到jsonpath特性

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