diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-05 12:11:53 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-05 12:11:53 +0100 |
| commit | b71d471254ffec672e9cf3815372f79db3247f32 (patch) | |
| tree | 62a834ed5038d2b8a17763b8238b4377728363ca | |
| parent | 70634b109b76dcb20b5e5622ba105d73e2136ef5 (diff) | |
| download | shrinker-b71d471254ffec672e9cf3815372f79db3247f32.tar.gz shrinker-b71d471254ffec672e9cf3815372f79db3247f32.zip | |
add created_at to participant
| -rw-r--r-- | queries/participant.sql | 3 |
1 files changed, 3 insertions, 0 deletions
diff --git a/queries/participant.sql b/queries/participant.sql index a65bdde..8b20858 100644 --- a/queries/participant.sql +++ b/queries/participant.sql @@ -1,7 +1,9 @@ WITH rosters AS (SELECT + (match.data->'data'->'attributes'->>'createdAt')::timestamp AS matchdate, JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster FROM match WHERE id=$1), participants AS (SELECT + matchdate, rosters.roster->'data'->>'id' AS rosterid, JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant FROM rosters) @@ -10,6 +12,7 @@ SELECT participant->'data'->>'id' AS "api_id", rosterid AS "roster_api_id", participant->'relations'->0->'data'->>'id' AS "player_api_id", +matchdate AS "created_at", participant->'data'->'attributes'->>'actor' AS "hero", (participant->'data'->'attributes'->'stats'->>'assists')::int AS "assists", |