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
|
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'
),
-- cleanup players
linked_players AS (
SELECT
JSONB_BUILD_OBJECT(
'data', players.data-'relationships'
) AS data
FROM players
),
-- link participant-player
linked_participants AS (
SELECT
JSONB_BUILD_OBJECT(
'data', participants.data-'relationships',
'relations', TO_JSONB(ARRAY(
SELECT * FROM linked_players
WHERE participants.data->'relationships'->'player'->'data' = JSONB_BUILD_OBJECT('id', linked_players.data->>'id', 'type', linked_players.data->>'type')
))
) 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->'data'->>'id', 'type', linked_participants.data->'data'->>'type'))
))
) AS data
FROM rosters
),
-- link match-rosters
linked_matches AS (
SELECT
matches.data->>'id' AS id,
JSONB_BUILD_OBJECT(
'data', matches.data-'relationships',
'relations', 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 data
FROM matches
),
-- insert!
insert_matches AS (
INSERT INTO match(id, data) SELECT * FROM linked_matches
ON CONFLICT(id) DO NOTHING
RETURNING id
)
SELECT id FROM insert_matches
|
