My tables:
questions
id: serial
content: text
tags
id: serial
name: text
questions_tags
question_id integer
tag_id integer
I need to select all questions with tag ‘css’, but don’t remove all other tags from JSON from jsonb_agg(). I have this SQL query:
SELECT q.id,
jsonb_agg(to_jsonb(tags)) AS tags
FROM questions q
LEFT JOIN questions_tags qt ON qt.question_id = q.id
LEFT JOIN tags ON tags.id = qt.tag_id
WHERE tags.name = 'css'
GROUP BY q.id
ORDER BY id
LIMIT 20
;
WHERE tags.name = 'css'
clause removes all other tags from result!
id | tags
-----+---------------------------
3 | [{"id": 3, "name": "css"}]
5 | [{"id": 3, "name": "css"}]
13 | [{"id": 3, "name": "css"}]
57 | [{"id": 3, "name": "css"}]
This questions have other tags, but WHERE clause removes it. How to avoid it?
I need something like this result:
id | tags
-----+------------------------------------------------------
3 | [{"id": 3, "name": "css"}, {"id": 5, "name": "html"}]
5 | [{"id": 3, "name": "css"}]
13 | [{"id": 3, "name": "css"}, {"id": 7, "name": "js"}]
57 | [{"id": 3, "name": "css"}]
2
Answers
You should first select the questions.id where tag.name = ‘css’ in a sub select and then do the jsonb_agg() in the surrounding SELECT where the columns.id are IN( the sub select where tag.name = ‘css’)
You seem to be quite close. The join logic is OK, but instead of filtering on the tag name in the
where
clause, you would need to use ahaving
clause, to check if any tag of the given question matches your search parameter:This way, all tags are retained as soon as any tag in the group matches, rather than just the matching tag being kept as in your original code.
Other changes to your code:
tags
)inner join
s rather thanleft join
s (you don’t need the latter)