1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
WITH rosters AS (SELECT
match.data->'data'->'attributes'->>'shardId' AS shardid,
(match.data->'data'->'attributes'->>'createdAt')::timestamp AS matchdate,
JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
FROM match WHERE id=$1),
participants AS (SELECT
shardid,
matchdate,
JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant
FROM rosters),
players AS (SELECT
shardid,
matchdate,
JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player
FROM participants)
SELECT
players.player->'data'->>'id' AS "api_id",
shardid AS "shard_id",
players.player->'data'->'attributes'->>'name' AS "name",
(players.player->'data'->'attributes'->'stats'->>'level')::int AS "level",
(players.player->'data'->'attributes'->'stats'->>'xp')::int AS "xp",
(players.player->'data'->'attributes'->'stats'->>'played')::int AS "played",
(players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "played_ranked",
(players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "played_casual",
(players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins",
(players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetime_gold",
matchdate AS "last_match_created_date",
0 AS "streak"
FROM players
|
