Filter SQL-result with WHERE and not filter in jsonb_agg() at the same time

Question

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"}]

Answer 1

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 a having clause, to check if any tag of the given question matches your search parameter:

SELECT q.id, jsonb_agg(to_jsonb(t)) AS tags
FROM questions q
INNER JOIN questions_tags qt ON qt.question_id = q.id
INNER JOIN tags t ON t.id = qt.tag_id
GROUP BY q.id
HAVING bool_or(t.name = 'css')
ORDER BY id
LIMIT 20

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:

• use table aliases everywhere (it was missing on tags)
• use inner joins rather than left joins (you don't need the latter)