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
34
35
36
37
38
39
40
41
42
43
44
|
#!/usr/bin/python
# TODO: read this from sql files
# TODO: index
create_index = """
CREATE INDEX ON match((data->>'id'));
CREATE INDEX ON roster((data->>'id'));
CREATE INDEX ON participant((data->>'id'));
CREATE INDEX ON match((data->'attributes'->>'createdAt'));
"""
matches = """
SELECT ARRAY_TO_JSON(ARRAY(
SELECT
JSONB_BUILD_OBJECT(
'id', match.data->>'id',
'date', match.data->'attributes'->>'createdAt',
'duration', CAST(
match.data->'attributes'->>'duration'
AS INTEGER),
'teams', ARRAY(
SELECT(
SELECT
JSONB_BUILD_OBJECT(
'id', roster.data->>'id',
'side', roster.data->'attributes'->'stats'->>'side',
'kills', roster.data->'attributes'->'stats'->>'heroKills',
'players', ARRAY(
SELECT(
SELECT
participant.data->'attributes'->>'actor'
FROM participant WHERE relparticipant->>'id' = participant.data->>'id')
FROM JSONB_ARRAY_ELEMENTS(roster.data->'relationships'->'participants'->'data') relparticipant
)
)
FROM roster WHERE relroster->>'id' = roster.data->>'id')
FROM JSONB_ARRAY_ELEMENTS(match.data->'relationships'->'rosters'->'data') relroster
)
)
FROM match
ORDER BY match.data->'attributes'->>'createdAt' DESC
)) AS data
"""
|
