i7gmFUJC

1id: 1,
2name: "my group",
3filters: [
4  {field: "sessions", type: "greater_than", value: 5}, 
5  {field: "email", type: "contains", value: "@example.com}
6]
7	
8id: 1, email: "example@gmail.com", sessions: 5, more_meta_data..
9	
10id: 1, matching_groups: ["my group"], email: "example@gmail.com", sessions: 5  more_meta_data..
11	
12create table groups (id int, name text, filters text[]);
13insert into groups values
14(1, 'group1', array['sessions > 0', 'email like ''example%''']),
15(2, 'group2', array['sessions > 5', 'email like ''example%''']),
16(3, 'group3', array['sessions = 5', 'email like ''%gmail.com''']);
17
18create table visitors (id int, email text, sessions int);
19insert into visitors values 
20(1, 'example@gmail.com', 5),
21(2, 'example@abc.com', 10),
22(3, 'me@gmail.com', 5),
23(4, 'xxx@yyy.com', 5);
24	
25create or replace function the_filter(filters text[], id int)
26returns boolean language plpgsql as $$
27declare 
28    r boolean;
29begin
30    execute format(
31        'select %s from visitors where id = %s',
32        array_to_string(filters, ' and '),
33        id)
34    into r;
35    return r;
36end $$;
37	
38select v.*, array_agg(g.name) groups
39from visitors v
40left join groups g on the_filter(g.filters, v.id)
41group by 1, 2, 3;
42
43 id |       email       | sessions |     groups      
44----+-------------------+----------+-----------------
45  1 | example@gmail.com |        5 | {group1,group3}
46  2 | example@abc.com   |       10 | {group1,group2}
47  3 | me@gmail.com      |        5 | {group3}
48  4 | xxx@yyy.com       |        5 | {NULL}
49(4 rows)