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

@Barmar Presto (which Athena is based on) has arrays. See https://prestosql.io/docs/current/functions/array.html — Piotr Findeisen, Oct 03 '20 at 21:48

score 1 · Answer 1 · answered Oct 03 '20 at 21:54

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

answered Oct 03 '20 at 21:54

Barmar

741,623
53
500
612

SQL Query and Array with IN function

1 Answers1