我有一個與會者表，其中有一個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以上，過濾邏輯如下：
- 選擇具有該
eventfilters欄位的所有與會者，以便：selected帶有field: "Org Type"參加者 (eventfilters)的陣列包含存在于selected帶有查詢過濾器欄位“Org Type”的陣列中的任何值； 和selected帶有field: "Industry Sector"與會者 (eventfilters)的陣列包含存在于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
uj5u.com熱心網友回復：
所以，我稍微瀏覽了一下，得到了以下查詢：
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，但似乎有效。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/335127.html
標籤：PostgreSQL jsonb postgresql-11 交叉连接 postgresql-json
上一篇：什么是.raw[0]