Skip to content

Instantly share code, notes, and snippets.

@phil-lopreiato
Created Sep 1, 2017
Embed
What would you like to do?
#standardSQL
SELECT match_key, red_score, blue_score, red_teams, blue_teams
FROM (
SELECT __key__.name as match_key, team_key_names,
JSON_EXTRACT_SCALAR(alliances_json, '$.red.score') as red_score,
JSON_EXTRACT_SCALAR(alliances_json, '$.blue.score') as blue_score,
REGEXP_EXTRACT_ALL(JSON_EXTRACT(alliances_json, '$.red.teams'), '"(frc[0-9]+)"') AS red_teams,
REGEXP_EXTRACT_ALL(JSON_EXTRACT(alliances_json, '$.blue.teams'), '"(frc[0-9]+)"') AS blue_teams
FROM `tbatv-prod-hrd.the_blue_alliance.match`
WHERE 'frc971' IN UNNEST(team_key_names) AND 'frc254' IN UNNEST(team_key_names))
WHERE 'frc971' IN UNNEST(red_teams) AND 'frc254' IN UNNEST(blue_teams)
OR 'frc971' IN UNNEST(blue_teams) AND 'frc254' IN UNNEST(red_teams)
ORDER BY match_key
LIMIT 100
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment