diff options
| author | Kapil Viren Ahuja <kvahuja@users.noreply.github.com> | 2017-02-27 19:12:59 +0530 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2017-02-27 19:12:59 +0530 |
| commit | 65f7c3cddaa7b4a495c283ccf7c9b415bb365f4b (patch) | |
| tree | b5c8df31ad2fce3fedcfc564297496d191d3b11f /queries/roster.sql | |
| parent | 2309c313b648e06b3c441ab8aafd845939dedc48 (diff) | |
| parent | 3575da2783ba62ddf3784ae814c8fec862c0eb1a (diff) | |
| download | shrinker-65f7c3cddaa7b4a495c283ccf7c9b415bb365f4b.tar.gz shrinker-65f7c3cddaa7b4a495c283ccf7c9b415bb365f4b.zip | |
Merge pull request #22 from vainglorygame/rewrite
rewrite to use joblib (fixes #20), use updated raw schema, check for …
Diffstat (limited to 'queries/roster.sql')
| -rw-r--r-- | queries/roster.sql | 31 |
1 files changed, 16 insertions, 15 deletions
diff --git a/queries/roster.sql b/queries/roster.sql index eaf0ed6..c61967b 100644 --- a/queries/roster.sql +++ b/queries/roster.sql @@ -1,17 +1,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", -id AS "apiId", -(attributes->'stats'->>'acesEarned')::int AS "acesEarned", -(attributes->'stats'->>'gold')::int AS "gold", -(attributes->'stats'->>'heroKills')::int AS "heroKills", -(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures", -attributes->'stats'->>'side' AS "side", -attributes->'stats'->>'side' AS "teamColor", -(attributes->'stats'->>'turretKills')::int AS "turretKills", -(attributes->'stats'->>'turretsRemaining')::int AS "turretsRemaining", - -relationships->'participants'->'data'->0->>'id' AS "participant_1", -relationships->'participants'->'data'->1->>'id' AS "participant_2", -relationships->'participants'->'data'->2->>'id' AS "participant_3" - -FROM "roster" +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 |