Return only rows containing a specified string in their string array in postgresql

Question

I'd like to get all of the groups of records that have a specific value (or set of values) within a string array in PostgresSQL. For example, take the following table

+---------+------------+--------+
| user_id |    date    | group  |
+---------+------------+--------+
|       1 | 2019-02-06 | groupA |
|       1 | 2019-02-06 | groupB |
|       1 | 2019-02-06 | groupC |
|       2 | 2019-02-06 | groupB |
|       2 | 2019-02-06 | groupC |
|       3 | 2019-02-06 | groupA |
|       3 | 2019-02-06 | groupC |
+---------+------------+--------+

I can group them with the following query:

SELECT
   user_id,
   date,
   string_agg(group, ',') as groups
FROM t
GROUP BY
   user_id,
   date

which gives the following;

+---------+------------+-------------------------+
| user_id |    date    |         groups          |
+---------+------------+-------------------------+
|       1 | 2019-02-06 |  {groupA,groupB,groupC} |
|       2 | 2019-02-06 |  {groupB,groupC}        |
|       3 | 2019-02-06 |  {groupA,groupC}        |
+---------+------------+-------------------------+

But I only want groups that contain groupA. That is, here is the desired output:

+---------+------------+-------------------------+
| user_id |    date    |         groups          |
+---------+------------+-------------------------+
|       1 | 2019-02-06 |  {groupA,groupB,groupC} |
|       3 | 2019-02-06 |  {groupA,groupC}        |
+---------+------------+-------------------------+

How do I filter out rows that don't contain groupA?

Here's one query I've tried unsuccessfully:

SELECT
   user_id,
   date,
   string_agg(group, ',') as groups
FROM t
GROUP BY
   user_id,
   date
HAVING 'groupA' IN string_agg(group, ',')

Answer 1

INcan't be used with lists of elements, string_agg() returns a single character value.

But you can aggregate the values into an array that can be used in the having clause:

SELECT user_id,
       "date",
       string_agg("group", ',') as groups
FROM t
GROUP BY user_id, "date"
HAVING 'groupA' = any (array_agg("group"));

Another option is to use the contains operator @>

SELECT user_id,
       "date",
       string_agg("group", ',') as groups
FROM t
GROUP BY user_id, date
HAVING array_agg("group") @> array['groupA']::varchar[]