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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
WITH
-- parse source string
srcjson AS (
SELECT $1::JSONB
AS data
),
-- split into data / included
matches AS (
SELECT JSONB_ARRAY_ELEMENTS(srcjson.data->'data') AS data FROM srcjson
),
includes AS (
SELECT JSONB_ARRAY_ELEMENTS(srcjson.data->'included') AS data FROM srcjson
),
-- filter included by type
rosters AS (
SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='roster'
),
participants AS (
SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='participant'
),
players AS (
SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='player'
),
-- link participant-player
linked_participants AS (
SELECT
JSONB_BUILD_OBJECT(
'data', participants.data - 'relationships',
'relations', participants.data->'relationships'->'player'
-- stop: don't embed player object, it will be put into a seperate table
-- instead, only reference the player ids in a jsonb array
) AS data
FROM participants
),
-- link roster-participants
linked_rosters AS (
SELECT
JSONB_BUILD_OBJECT(
'data', rosters.data - 'relationships',
'relations', TO_JSONB(ARRAY(
SELECT * FROM linked_participants
WHERE rosters.data->'relationships'->'participants'->'data' @> JSONB_BUILD_ARRAY(JSONB_BUILD_OBJECT('id', linked_participants.data->>'id', 'type', linked_participants.data->>'type'))
))
) AS data
FROM rosters
),
-- link match-rosters
linked_matches AS (
SELECT
matches.data->>'id' AS id,
matches.data->>'type' AS type,
matches.data->'attributes' AS attributes,
TO_JSONB(ARRAY(
SELECT * FROM linked_rosters
WHERE matches.data->'relationships'->'rosters'->'data' @> JSONB_BUILD_ARRAY(JSONB_BUILD_OBJECT('id', linked_rosters.data->'data'->>'id', 'type', linked_rosters.data->'data'->>'type'))
)) AS relations
FROM matches
),
-- cleanup players
linked_players AS (
SELECT
players.data->>'id' AS id,
players.data->>'type' AS type,
players.data->'attributes' AS attributes
FROM players
),
-- insert!
insert_matches AS (
INSERT INTO match SELECT * FROM linked_matches
ON CONFLICT(id) DO NOTHING
RETURNING type, id -- TODO conflict shouldn't happen in prod
),
insert_players AS (
INSERT INTO player SELECT * FROM linked_players
ON CONFLICT(id) DO
UPDATE SET attributes=EXCLUDED.attributes
WHERE (player.attributes->'stats'->>'played')::int < (EXCLUDED.attributes->'stats'->>'played')::int
RETURNING type, id
)
SELECT type, id FROM insert_matches
UNION
SELECT type, id FROM insert_players
|
