Find by multiple columns and rows criteria sql query

I have Paths and Criteria tables in PostgreSQL. Paths have multiple criteria that should be fulfilled to be selected.

Path table with (id, name), Criteria table with (id, key, value, path_id)

I need to create an SQL query for selecting a single path that fulfills two or more criteria inputs (for example, age of 30 and male gender). I have tried ANY as given below.

SELECT p.*, array_agg(c.*) as criteria 
FROM paths as p 
INNER JOIN criteria as c ON c.path_id = p.id 
WHERE (c.key, c.value) = ANY (array[
    ("age","30"),
    ("gender","male") 
    ])
GROUP BY p.id

But this gets a path whenever any one of the criteria is met (whether age = 30 or sex = "male" not both). I replaced ANY with ALL but that doesn’t return any value at all.

Answer

If I followed you correctly, you need to move the conditions to the having clause instead of the where clause. Also if you want the records that satisfy all conditions, then you need to check for each condition individually:

select p.*, array_agg(c.key || ':' || c.value) as criteria
from paths p
inner join criteria c on c.path_id = p.id
group by p.id
having
    count(*) filter(where c.key = 'age' and c.value = '30') > 0
    and count(*) filter(where c.key = 'gender' and c.value = 'male') > 0

This can also be expressed with two EXISTS conditions, which should be a quit efficient query with an index on criteria(path_id, key, value) (but then you loose the ability to aggregate all the criteria):

select p.*
from paths p
where
    exists (
        select 1 
        from criteria c
        where c.path_id = p.id and c.key = 'age' and c.value = '30'
    )
    and exists (
        select 1 
        from criteria c
        where c.path_id = p.id and c.key = 'gender' and c.value = 'male'
    )