PostgreSQL – How to find the row with a record that matches it with a value higher than given value?

Let’s say I have two tables with a 1-to-many relation.

Table A (user): id INT, name TEXT

Table B (skill): id INT, user_id INT, skill_name TEXT, skill_level INT

Each user may have multiple skills. And now I wish to gather the users that have a certain skill that at least at a certain level, and maybe the users that have all the skills that matches the condition.

For example, let’s say I have the following data in my database:

User:

id | name 
1  | Merlin
2  | Morgan

Skill:

id | user_id | skill_name    | skill_level 
1  | 1       | Fireball      | 2 
2  | 1       | Thunderbolt   | 3 
2  | 2       | Thunderbolt   | 2 
2  | 2       | Firestorm     | 1 
2  | 2       | Curse         | 3 

And if I search for user who has thunderbolt at level 2 or more, I should get both Merlin and Morgan; if if I search for user who knows both thunderbolt at level 1 or more and curse at level 2 or more, then only Morgan should appear.

Also, I am hoping the result could also contain the content of all skills the users have. For now I am using ARRAY_AGG(JSON_BUILD_OBJECT('skill_name', skill_name, 'skill_level', skill_level') to gather all skills by users’ id. But I don’t know how to filter the data based on those skills I get.

Answer

One approach uses aggregation, and a having clause to filter on skills names and levels. Boolean aggregate functions come handy here:

select u.*, 
    jsonb_agg(jsonb_build_object('skill_name', skill_name, 'skill_level', skill_level)) as skills
from users u
inner join skills s on s.user_id = u.id
group by u.id
having bool_or(s.skill_name = 'Thunderbolt' and s.skill_level >= 1)
   and bool_or(s.skill_name = 'Curse'       and s.skill_level >= 2)

This also adds a column to the resultset, called skills, that is a JSONB array containing one object for each skill name and level, as requested in your question: note that it makes more sense to generate a JSON(B) array rather than an array of JSON objects, as your originally intended.