blob: c61967b7bbdef33058cd712b4ac5881b24fb0989 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
WITH rosters AS (SELECT
JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
FROM match WHERE id=$1)
SELECT
roster->'data'->>'id' as "apiId",
(roster->'data'->'attributes'->'stats'->>'acesEarned')::int AS "acesEarned",
(roster->'data'->'attributes'->'stats'->>'gold')::int AS "gold",
(roster->'data'->'attributes'->'stats'->>'heroKills')::int AS "heroKills",
(roster->'data'->'attributes'->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
roster->'data'->'attributes'->'stats'->>'side' AS "side",
roster->'data'->'attributes'->'stats'->>'side' AS "teamColor",
(roster->'data'->'attributes'->'stats'->>'turretKills')::int AS "turretKills",
(roster->'data'->'attributes'->'stats'->>'turretsRemaining')::int AS "turretsRemaining",
roster->'relations'->0->'data'->>'id' AS "participant_1",
roster->'relations'->1->'data'->>'id' AS "participant_2",
roster->'relations'->2->'data'->>'id' AS "participant_3"
FROM rosters
|
