Count matches between multiple columns and words in a nested array

Question

My earlier question was resolved. Now I need to develop a related, but more complex query.

I have a table like this:

id     description          additional_info
-------------------------------------------
123    games                XYD
124    Festivals sport      swim

And I need to count matches to arrays like this:

array_content varchar[] := {"Festivals,games","sport,swim"}

If either of the columns description and additional_info contains any of the tags separated by a comma, we count that as 1. So each array element (consisting of multiple words) can only contribute 1 to the total count.

The result for the above example should be:

id    RID    Matches
1     123    1
2     124    2

I have read it twice but did not understand what you need exactly. Please clarify. — Erwin Brandstetter, Nov 02 '16 at 16:09
@erwin : I need to match the description and name with the array elements. Over here if any of the tags(separated by comma) present on each array position matches the desc. or title then we consider it as matched = 1. The query which we were executing earlier had only one tag on each array position which was easily separated by unnset. — Himanshu Mohan, Nov 02 '16 at 16:22

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

The answer isn't simple, but figuring out what you are asking was harder:

SELECT row_number() OVER (ORDER BY t.id) AS id
     , t.id AS "RID"
     , count(DISTINCT a.ord) AS "Matches"
FROM   tbl t
LEFT   JOIN (
   unnest(array_content) WITH ORDINALITY x(elem, ord)
   CROSS JOIN LATERAL
   unnest(string_to_array(elem, ',')) txt
   ) a ON t.description ~ a.txt
       OR t.additional_info ~ a.txt
GROUP  BY t.id;

Produces your desired result exactly.
array_content is your array of search terms.

How does this work?

Each array element of the outer array in your search term is a comma-separated list. Decompose the odd construct by unnesting twice (after transforming each element of the outer array into another array). Example:

SELECT *
FROM   unnest('{"Festivals,games","sport,swim"}'::varchar[]) WITH ORDINALITY x(elem, ord)
CROSS  JOIN LATERAL
       unnest(string_to_array(elem, ',')) txt;

Result:

 elem            | ord |  txt
-----------------+-----+------------
 Festivals,games | 1   | Festivals
 Festivals,games | 1   | games
 sport,swim      | 2   | sport
 sport,swim      | 2   | swim

Since you want to count matches for each outer array element once, we generate a unique number on the fly with WITH ORDINALITY. Details:

PostgreSQL unnest() with element number

Now we can LEFT JOIN to this derived table on the condition of a desired match:

   ... ON t.description ~ a.txt
       OR t.additional_info ~ a.txt

.. and get the count with count(DISTINCT a.ord), counting each array only once even if multiple search terms match.

Finally, I added the mysterious id in your result with row_number() OVER (ORDER BY t.id) AS id - assuming it's supposed to be a serial number. Voilá.

The same considerations for regular expression matches (~) as in your previous question apply:

Postgres query to calculate matching strings

Over here what if we want to match both of Festivals AND Games. If both of the tags "Festivals, games" matched then only count it as matched! — Syed Asad Abbas Zaidi, Nov 04 '16 at 07:09
http://stackoverflow.com/questions/40457813/postgres-from-query-with-one-of-the-column-name — Syed Asad Abbas Zaidi, Nov 07 '16 at 04:21
http://stackoverflow.com/questions/40460128/aggregator-functions-not-allowed-in-functions-in-from — Syed Asad Abbas Zaidi, Nov 07 '16 at 07:44

Count matches between multiple columns and words in a nested array

1 Answers1

How does this work?

Linked