Extract existing mentions from bio

PostgreSQL
1

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.

Query Result

name bio mentions
John Doe Ex - @meta | @tesla | @abc [“meta”, “tesla”]

Profile Table

name bio
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”]

1 Like
2

Hello @Shreyas_Chorge , thank you for writing us!

Looking at your query, it is necessary to add WHERE EXISTS to ensure that each element in “mentions” is within the profile table

Like this example:

test=# select * from profile;
 id |   name   |           bio
----+----------+--------------------------
  1 | John Doe | Ex - @meta, @tesla, @abc
  2 | meta     | We do social media
  3 | tesla    | We make cool stuff

test=# SELECT
  p.name,
  p.bio,
  ARRAY(
    SELECT DISTINCT mention
    FROM (
      SELECT UNNEST(regexp_matches(p.bio, '@([a-zA-Z0-9_]+)', 'g')) AS mention
    ) AS subquery
    WHERE EXISTS (
      SELECT 1 FROM profile pwg WHERE pwg.name = subquery.mention
    )
  ) AS mentions
FROM profile p
WHERE p.name = 'John Doe';
   name   |           bio            |   mentions
----------+--------------------------+--------------
 John Doe | Ex - @meta, @tesla, @abc | {meta,tesla}
(1 row)

I hope that helps!