From 3c420196975ac8bd5f77fefffc1539eba43e2785 Mon Sep 17 00:00:00 2001 From: schneefux Date: Mon, 27 Feb 2017 15:59:42 +0100 Subject: reference from participant to roster --- queries/participant.sql | 2 ++ 1 file changed, 2 insertions(+) (limited to 'queries/participant.sql') diff --git a/queries/participant.sql b/queries/participant.sql index e11179c..7022d90 100644 --- a/queries/participant.sql +++ b/queries/participant.sql @@ -2,11 +2,13 @@ WITH rosters AS (SELECT JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster FROM match WHERE id=$1), participants AS (SELECT + rosters.roster->'data'->>'id' AS rosterid, JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant FROM rosters) SELECT participant->'data'->>'id' AS "apiId", +rosterid AS "roster_apiId", participant->'relations'->0->'data'->>'id' AS "player_apiId", participant->'data'->'attributes'->>'actor' AS "hero", -- cgit v1.3.1