I have a profile table with name, bio and other fields (mentions is not one of them).
How can I write a query in postgress that select all the rows based on certain condition (say name=“John Doe” in this case) and get all the mentions that is the exists in profile table.
|John Doe||Ex - @meta | @tesla | @abc||[“meta”, “tesla”]|
|John Doe||Ex - @meta, @tesla, @abc|
|meta||We do social media|
|tesla||We make cool stuff|
I tried following query
SELECT *, ARRAY( SELECT DISTINCT unnest(regexp_matches(p.bio, '@([a-zA-Z0-9_]+)', 'g')) FROM profile pwg WHERE pwg.id = p.id ) AS mentions FROM profile p where name = "John Doe"
But this simply extracts all the mentions i.e [“meta”, “tesla”, “abc”].
How can I write a query that can extract only those mentions that exists in profile table ie [“meta”, “tesla”]