Select empty column if regexp_match fails

Question

I'm trying to select all entities, even if they're not match expression, but this one returns only 'true' values.

SELECT entity_id, regexp_matches(error_params, '"select_flight"') IS NOT NULL
FROM MyTable
GROUP BY 1

Is there a way do solve such issue inside SELECT statement or in this case we should use LEFT JOIN to table with regexp_matches result?

Answer 1

If I'm understanding your question correctly, you can use count with case:

select entity_id,
       count(case when error_params like '%select flight%' then 1 end) cnt
from mytable
group by 1