PostgreSQL - filter results with a LIKE on an aggregate field (string_agg)

Question

I created an SQLFiddle scenario here but briefly:

I need to aggregate the contents of a one to many relationship into a long string and then i need to filter those result by comparing the content of that aggregate field against another string ( or rather seeing if the search string appears anywhere in the long aggregate string).

Help?

SELECT 
  Names.UserId, 
  Names.name, 
  string_agg(UserRoles.Rolename, ', ') as Roles
FROM 
  Names, UserRoles
WHERE 
  names.UserId = UserRoles.UserId
/*
** This ↓ dowsn't work
* ERROR: column "roles" does not exist
*/
/*
AND 
  Roles LIKE '%Ma%'
*/

GROUP BY 
  (Names.UserId, Names.name)
ORDER BY 
  Names.UserId;




CREATE TABLE UserRoles
    (UserId int, Rolename varchar(15))
;

CREATE TABLE Names
    (UserId int, Name varchar(15))
;

INSERT INTO UserRoles
    (UserId, Rolename)
VALUES
    (1, 'Manager'),
    (1, 'Event Organiser'),
    (2, 'Supervisor'),
    (2, 'Employee'),
    (2, 'Some otherRole')
;

INSERT INTO Names
    (UserId, Name)
VALUES
    (1, 'Jacob'),
    (2, 'Mary');

Answer 1

You could use a WITH clause.

WITH table_name AS (SELECT 
  Names.UserId, 
  Names.name, 
  string_agg(UserRoles.Rolename, ', ') as Roles
FROM 
  Names, UserRoles
WHERE 
  names.UserId = UserRoles.UserId
GROUP BY 
  (Names.UserId, Names.name)
ORDER BY 
  Names.UserId)
  
SELECT
*
FROM table_name
WHERE Roles LIKE '%Ma%';