I have the attendee table that has a jsonb
array field called eventfilters
.
Given the query filter array of the same signature (form) as the eventfilters
, I need to select the attendees filtered against this eventfilters
field by a query filter value.
Below is the example of both the query filter and eventfilters
field:
The eventfilters
field looks like:
[
{
"field": "Org Type",
"selected": ["B2C", "Both", "Nonprofit"]
},
{
"field": "Job Role",
"selected": ["Customer Experience", "Digital Marketing"]
},
{
"field": "Industry Sector",
"selected": ["Advertising", "Construction / Development"]
}
]
The query filter can look like this:
[
{
"field": "Org Type",
"selected": ["B2C", "Nonprofit"]
},
{
"field": "Industry Sector",
"selected": ["Advertising"]
}
]
So, both the eventfilters
field and the query filter are always of the same signature:
Array<{"field": text, "selected": text[]}>
Given the query filter and eventfilters
from the above, the filtering logic would be the following:
- Select all attendees that have the
eventfilters
field such that:- the
selected
array with thefield: "Org Type"
of the attendee (eventfilters
) contains any of the values that are present in theselected
array with the field "Org Type" of the query filter; and - the
selected
array with thefield: "Industry Sector"
of the attendee (eventfilters
) contains any of the values that are present in theselected
array with the field "Industry Sector" of the query filter.
- the
The query filter array can be of different length and have different elements, but always with the same signature (form).
What I could come up with is the logic stated above but not with the and
for each element in the query filter, but with the 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'])
);
Basically I need to change that or
in the where
clause in the query above to and
, but this, obviously, will not work.
The where
clause will be generated dynamically.
Here's the example of how I generate it now (it's javascript, but I hope you can grasp the idea):
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 ');
}
The simple swap of or
and and
in logic seems very different in the implementation. I guess it would be easier to implement it with the use of jsonpath
, but my postgres version is 11 :(
How can I implement such filtering?
PS: create table
and insert
code for reproducing: https://pastebin.com/1tsHyJV0
CodePudding user response:
So, I poked around a little bit and got the following query:
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']
);
It's a bit weird, especially the part with jsonb_agg
, but seems like it works.