Can I count the occurences for postgres array field?

Question

I have a table postgres that uses the array type of data, it allows some magic making it possible to avoid having more tables, but the non-standard nature of this makes it more difficult to operate with for a beginner. I would like to get some summary data out of it.

Sample content:

CREATE TABLE public.cts (
    id serial NOT NULL,
    day timestamp NULL,
    ct varchar[] NULL,
    CONSTRAINT ctrlcts_pkey PRIMARY KEY (id)
);

INSERT INTO public.cts
(id, day, ct)
VALUES(29, '2015-01-24 00:00:00.000', '{ct286,ct281}');
INSERT INTO public.cts
(id, day, ct)
VALUES(30, '2015-01-25 00:00:00.000', '{ct286,ct281}');
INSERT INTO public.cts
(id, day, ct)
VALUES(31, '2015-01-26 00:00:00.000', '{ct286,ct277,ct281}');

I would like to get the totals per array member occurence totalized, with an output like this for example:

name | value 
ct286 | 3
ct281 | 3
ct277 | 1

ct277 has a count of 1, not 2.

GMB
– GMB

2019-09-19 14:15:19 +00:00
Commented Sep 19, 2019 at 14:15 — GMB
– GMB, Commented Sep 19, 2019 at 14:15
@gmb that's why I need a sql way to do it :)

aseques
– aseques

2019-09-19 14:26:05 +00:00
Commented Sep 19, 2019 at 14:26 — aseques
– aseques, Commented Sep 19, 2019 at 14:26

GMB · Accepted Answer · 2019-09-19 14:12:28Z

3

Use Postgres function array unnest():

SELECT name, COUNT(*) cnt
FROM cts, unnest(ct) as u(name)
GROUP BY name

Demo on DB Fiddle:

| name  | cnt |
| ----- | --- |
| ct277 | 1   |
| ct281 | 3   |
| ct286 | 3   |

answered Sep 19, 2019 at 14:12

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can I count the occurences for postgres array field?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related