Find the number of occurrences in an array using another array?

Question

Let's say there is a base:

CREATE TABLE test (
    ids integer[]
);

With data

INSERT INTO test VALUES ('{1, 2, 3, 4}');
INSERT INTO test VALUES ('{5}');

I would like to find the number of occurrences of an arbitrary array, say: '{5,1}' in the test database, in what way can this be done?

In my attempts, I could only find the total number of elements in the array, except for those found:

select sum(array_length(array(
    (select unnest(ids) except select unnest(array[5,1]))
), 1)) from test;

But, i need exactly the number of occurrences found from the transferred array. How i can find . that?

If one row contained both 1 and 5, should that be counted once or twice? — jjanes
– jjanes, Commented Oct 24, 2019 at 16:16
@jjanes Only once. For example, in the example that's in the post, the answer should be: 2 — Uebanchik
– Uebanchik, Commented Oct 24, 2019 at 16:22

jjanes · Accepted Answer · 2019-10-24 16:25:45Z

3

You possibly want this:

select count(*) from test, unnest(ids) as id where id =ANY('{5,1}');

Or to count each row from test at most once, then this:

select count(*) from test where ids && '{5,1}'

They both give '2' in your example, but they don't do the same thing in general.

answered Oct 24, 2019 at 16:25

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

1 Answer 1