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 games
WHERE 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 games
WHERE 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,result
FROM id_and_names
WHERE name = 'PlayerF'