diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-27 20:58:21 +0200 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-27 20:58:21 +0200 |
| commit | 3c7b19d77476d774762b4a6c97bff18bcd0b249a (patch) | |
| tree | 32fbc9f93eacbc46b073920801b5ee79ba05fa0e /insert.sql | |
| parent | 60a8aac5f7acc61432fc6a59843e2c91d5df6a35 (diff) | |
| download | apigrabber-3c7b19d77476d774762b4a6c97bff18bcd0b249a.tar.gz apigrabber-3c7b19d77476d774762b4a6c97bff18bcd0b249a.zip | |
use 2.0 joblib
Diffstat (limited to 'insert.sql')
| -rw-r--r-- | insert.sql | 74 |
1 files changed, 0 insertions, 74 deletions
diff --git a/insert.sql b/insert.sql deleted file mode 100644 index bc6485d..0000000 --- a/insert.sql +++ /dev/null @@ -1,74 +0,0 @@ -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->'data'->>'id', 'type', linked_players.data->'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 DISTINCT id FROM insert_matches |