2

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]
James Z
  • 12,209
  • 10
  • 24
  • 44

1 Answers1

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]
Guru Stron
  • 102,774
  • 10
  • 95
  • 132