SQL: split string and count occurances by week

Question

I've written a SQL query that looks like this:

SELECT date_trunc('week', created_at)::date AS week, posted_to AS providers
FROM documents
GROUP BY week, providers
ORDER BY week, providers

A typical row out looks like this:

2017-05-08  {facebook_2214,facebook_2215,facebook_2216,twitter_2219}

I want to count the number of times the word facebook or twitter or linkedin or others occurs per week, so to group by facebook for this week should return 3, and twitter for this week should return 1. The column type is an array.

Any ideas? I'd rather it be programatic than write an AS facebook for each possible result, but beggars can't be choosers.

Answer 1

Extract a provider name using split_part() and count sum in groups by date, provider:

with my_data(date, providers) as (
values
    ('2017-05-08', '{facebook_2214,facebook_2215,facebook_2216,twitter_2219}'::text[]),
    ('2017-05-15', '{twitter_2220,twitter_2221,linkedin_2222}')
)

select date, provider, count(*)
from (
    select date, split_part(u, '_', 1) as provider
    from my_data,
    unnest(providers) u
    ) s
group by 1, 2
order by 1, 2;

    date    | provider | count 
------------+----------+-------
 2017-05-08 | facebook |     3
 2017-05-08 | twitter  |     1
 2017-05-15 | linkedin |     1
 2017-05-15 | twitter  |     2
(4 rows)