Find rows in a table where a foreign key is in an array

Fact information for an event may be associated with multiple entities, and the associated entities could be stored in an array. A common example would be team sports of some kind, and statistics about a game. The game has multiple players, so the array would contain the IDs of all the players on the team. The information about the player is in a separate table. If the requirement is to get all the games for a particular player, the usual JOIN approach will not work. Instead, either CONTAINS, FILTER, or UNNEST are viable options.

CONTAINS is generally the faster, more efficient approach.

The examples below show queries to get the games PlayerF was in, all the players involved, and whether the game was a win or a loss. The results are that the player was in 2 games, with one game being a win and the other game being a loss.

contains example

WITH games AS (    SELECT [1,2,3,4,5] AS player_array,'win' as result    UNION ALL    SELECT [1,2,3,4,6] AS player_array,'loss' as result    UNION ALL    SELECT [1,2,3,6,7] AS player_array,'win' as result),names AS (    SELECT 1 AS id, 'PlayerA' AS name    UNION ALL    SELECT 2 AS id, 'PlayerB' AS name    UNION ALL    SELECT 3 AS id, 'PlayerC' AS name    UNION ALL    SELECT 4 AS id, 'PlayerD' AS name    UNION ALL    SELECT 5 AS id, 'PlayerE' AS name    UNION ALL    SELECT 6 AS id, 'PlayerF' AS name)SELECT *FROM gamesWHERE CONTAINS(player_array,    (    SELECT id     FROM names     WHERE name = 'PlayerF'    )) 

filter example

WITH games AS (    SELECT [1,2,3,4,5] AS player_array,'win' as result    UNION ALL    SELECT [1,2,3,4,6] AS player_array,'loss' as result    UNION ALL    SELECT [1,2,3,6,7] AS player_array,'win' as result),names AS (    SELECT 1 AS id, 'PlayerA' AS name    UNION ALL    SELECT 2 AS id, 'PlayerB' AS name    UNION ALL    SELECT 3 AS id, 'PlayerC' AS name    UNION ALL    SELECT 4 AS id, 'PlayerD' AS name    UNION ALL    SELECT 5 AS id, 'PlayerE' AS name    UNION ALL    SELECT 6 AS id, 'PlayerF' AS name)SELECT *FROM gamesWHERE FILTER(x -> x = (    SELECT id     FROM names     WHERE name = 'PlayerF'    ),player_array) <> '[]' 

unnnest example

WITH games AS (    SELECT [1,2,3,4,5] AS player_array,'win' as result    UNION ALL    SELECT [1,2,3,4,6] AS player_array,'loss' as result    UNION ALL    SELECT [1,2,3,6,7] AS player_array,'win' as result),names AS (    SELECT 1 AS id, 'PlayerA' AS name    UNION ALL    SELECT 2 AS id, 'PlayerB' AS name    UNION ALL    SELECT 3 AS id, 'PlayerC' AS name    UNION ALL    SELECT 4 AS id, 'PlayerD' AS name    UNION ALL    SELECT 5 AS id, 'PlayerE' AS name    UNION ALL    SELECT 6 AS id, 'PlayerF' AS name),id_and_names as (SELECT * FROM    (SELECT *     FROM games  UNNEST(player_array as single_id)) ia    INNER JOIN names n         ON n.id = ia.single_id)SELECT player_array,resultFROM id_and_namesWHERE name = 'PlayerF'