postgresqljsonbpostgresql-11cross-joinpostgresql-json

Filter by a nested jsonb array column against the array of the same signature

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:

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

Solution

  • 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.