How to count the number of elements in one array (with duplicates) that match with elements of another array in SQL(Presto)?

Question

I have two arrays X,Y. X=[a,b,c] and Y=[a,a,b,b,b,c,d,d,e,e,e]. I want to write a query that will return the number of elements in Y that match the elements in X (with duplicates). in this case the out put should be [a,a,b,b,b,c] and I need the length of this array which is 6. I know array_intersect will return with no duplicates.

SELECT array_intersect([a,b,c],[a,a,b,b,b,c,d,d,e,e,e])

the result is

[a,b,c]

but my desired output is

[a,a,b,b,b,c]

Answer 1

This can be achieved with filter and contains:

SELECT filter(array['a','a','b','b','b','c','d','d'], el -> contains(array['a','b','c'], el))

Output:

_col0
[a, a, b, b, b, c]