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]