根据相同签名的数组通过嵌套的jsonb数组列进行筛选

Question

我有一个与会者表，它有一个名为eventfilters的jsonb数组字段。

给定与eventfilters相同签名(形式)的查询过滤器数组，我需要通过查询过滤器值选择针对此eventfilters字段过滤的与会者。

---

下面是查询过滤器和eventfilters字段的示例:

eventfilters字段如下:

[
{
"field": "Org Type",
"selected": ["B2C", "Both", "Nonprofit"]
},
{
"field": "Job Role",
"selected": ["Customer Experience", "Digital Marketing"]
},
{
"field": "Industry Sector",
"selected": ["Advertising", "Construction / Development"]
}
]

查询过滤器可以像这样:

[
{
"field": "Org Type",
"selected": ["B2C", "Nonprofit"]
},
{
"field": "Industry Sector",
"selected": ["Advertising"]
}
]

因此，eventfilters字段和查询过滤器总是具有相同的签名:

Array<{"field": text, "selected": text[]}>

---

给定上面的查询过滤器和eventfilters，过滤逻辑将如下所示:

• 选择eventfilters字段的所有与会者，如下所示:
  • 包含与会者(eventfilters)的field: "Org Type"的selected数组包含selected数组中带有"组织类型"字段的任何值。查询过滤器的;和
  • 包含与会者(eventfilters)的field: "Industry Sector"的selected数组包含selected数组中包含"行业部门"字段的任何值。

查询过滤器数组可以有不同的长度和不同的元素，但必须具有相同的签名(形式)。

---

我能想到的是上面所述的逻辑，但不是查询过滤器中每个元素的and，而是or:

select distinct attendee.id,
attendee.email,
attendee.eventfilters
from attendee cross join lateral jsonb_array_elements(attendee.eventfilters) single_filter
where (
((single_filter ->> 'field')::text = 'Org Type' and (single_filter ->> 'selected')::jsonb ?| array ['B2C', 'Nonprofit'])
or ((single_filter ->> 'field')::text = 'Industry Sector' and (single_filter ->> 'selected')::jsonb ?| array ['Advertising'])
);

基本上我需要改变or在where子句在上面的查询and，但这显然是行不通的。

where子句将动态生成

下面是我现在如何生成它的例子(它是javascript，但我希望你能掌握这个想法):

function buildEventFiltersWhereSql(eventFilters) {
return eventFilters.map((filter) => {
const selectedArray = filter.selected.map((s) => `'${s}'`).join(', ');
return `((single_filter ->> 'field')::text = '${filter.field}' and (single_filter ->> 'selected')::jsonb ?| array[${selectedArray}])`;
}).join('nor ');
}

---

or和and在逻辑上的简单交换在实现上看起来有很大的不同。我想使用jsonpath来实现它会更容易，但我的postgres版本是11:(

如何实现这样的过滤?

---

PS:create table和insert复制代码:https://pastebin.com/1tsHyJV0

Answer 1

所以，我稍微检查了一下，得到了以下查询:

with unnested_filters as (
select distinct attendee.id,
jsonb_agg((single_filter ->> 'selected')::jsonb) filter (where (single_filter ->> 'field')::text = 'Org Type') over (partition by attendee.id) as "Org Type",
jsonb_agg((single_filter ->> 'selected')::jsonb) filter (where (single_filter ->> 'field')::text = 'Industry Sector') over (partition by attendee.id) as "Industry Sector",
attendee.email,
attendee.eventid,
attendee.eventfilters
from attendee
cross join lateral jsonb_array_elements(attendee.eventfilters) single_filter
where eventid = 1
) select * from unnested_filters where (
(unnested_filters."Org Type" #>> '{0}')::jsonb ?| array['B2C', 'Both', 'Nonprofit']
and (unnested_filters."Industry Sector" #>> '{0}')::jsonb ?| array['Advertising']
);

这有点奇怪，特别是与jsonb_agg的部分，但似乎是有效的。