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

Question

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

给定与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，过滤逻辑如下：

and

• the

• 选择具有eventfilters字段的所有与会者:具有出席者field: "Org Type"的selected数组(eventfilters)包含在selected数组中的任何值，其字段为查询筛选器的"Org Type“；

• d24数组，其field: "Industry Sector"包含在selected数组中与查询filter.

的”工业扇区“字段一起存在的任何值。

查询筛选器数组可以具有不同的长度和不同的元素，但始终具有相同的签名(表单)。

我能得出的是上面所述的逻辑，但不是针对查询过滤器中的每个元素使用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有关的部分，但似乎很有效。