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