himanshu - 1 year ago 80

SQL Question

My earlier question was resolved, but now I need to develop more complex queries.

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`

`additional_info`

The result for the above example should be:

`id RID Matches`

1 123 1

2 124 2

Answer Source

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.

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:

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: