SQL Query and Array with IN function

Question

I have an array that I need to match with another dimension (VARCHAR). I need a User_ID to match against a list of the below Test_IDs, however the Test ID will always vary so I can't hard code in the variables.

Example array with the dimension

TestID[14782273,10904275,1454477270,16526331,1323009,1450722,529479190,1195598]

---

SUM(CASE
    WHEN User_ID IN TEST_ID
           THEN 1
        ELSE
        0
      END)
      AS Complete_Items

Answer 1

The documentation of array functions are here, and the array_position() function is useful here.

SUM(CASE
    WHEN array_position(TEST_ID, User_ID) > 0 THEN 1
    ELSE 0
    END) AS Complete_Items