Postgres From Query With One Of The Column Name

As follow-up to the previous question: Count matches between multiple columns and words in a nested array I have the following query: SELECT row_number() OVER (ORDER BY t.id) AS

Solution 1:

For now it is giving an exception that column name not defined.

That's because you are using a different column name a.obj_element. In the subquery, we named the column elem. (Or did you really mean to use txt?) So:

SELECTrow_number() OVER (ORDERBY t.id) AS id
     , t.id AS "RID"
     , array_agg(DISTINCT a.elem) AS array_values  -- or a.txt?
     , count(DISTINCT a.ord) AS "Matches"
FROM   tbl t
LEFTJOIN (
   unnest(array_content) WITH ORDINALITY x(elem, ord)
   CROSSJOINLATERALunnest(string_to_array(elem, ',')) txt
   ) a ON t.description ~ a.txt
       OR t.additional_info ~ a.txt
GROUPBY t.id;