-
-
Save giseburt/a22b6a90cbf141a1a5ea4754dfe220d1 to your computer and use it in GitHub Desktop.
Managing Complex Data with JSON in PostgreSQL, part 3
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP TABLE IF EXISTS people_raw; | |
CREATE TABLE people_raw ( | |
first_name text, | |
last_name text, | |
characteristics jsonb, | |
id uuid default gen_random_uuid(), | |
PRIMARY KEY (id) | |
); | |
DROP TABLE IF EXISTS enemy_association; | |
CREATE TABLE enemy_association ( | |
person_id uuid NOT NULL | |
REFERENCES people_raw (id) | |
ON DELETE CASCADE DEFERRABLE, | |
enemy_id uuid NOT NULL | |
REFERENCES people_raw (id) | |
ON DELETE CASCADE DEFERRABLE, | |
flavor jsonb, | |
id uuid default gen_random_uuid(), | |
PRIMARY KEY (id) | |
); | |
INSERT INTO people_raw | |
SELECT * | |
FROM json_populate_recordset (NULL::people, ' | |
[ | |
{ | |
"id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4", | |
"last_name": "Castanza", | |
"first_name": "George", | |
"characteristics": { | |
"favorites": { | |
"holidays": [ | |
"Summer of George" | |
] | |
} | |
} | |
}, | |
{ | |
"id": "6ae1da27-0565-4649-96c5-e4c1da48de21", | |
"last_name": "Kramer", | |
"first_name": "Cosmo", | |
"characteristics": { | |
"inventions": [ | |
"Coffee Table Book", | |
"The Bro" | |
] | |
} | |
}, | |
{ | |
"id": "a4adc3fd-485f-4fc7-a561-863ea78e9bb9", | |
"last_name": "Mr.", | |
"first_name": "Heyman", | |
"characteristics": { | |
"occupation": "Gym Teacher (Fired)" | |
} | |
}, | |
{ | |
"id": "5d7994d2-7960-4d73-9e1d-b4c255703765", | |
"last_name": "Joe", | |
"first_name": "Davola", | |
"characteristics": {} | |
} | |
]'); | |
INSERT INTO enemy_association | |
SELECT person_id, enemy_id, flavor | |
FROM json_populate_recordset (NULL::enemy_association, ' | |
[ | |
{ | |
"person_id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4", | |
"enemy_id": "5d7994d2-7960-4d73-9e1d-b4c255703765", | |
"flavor": "Unexplained" | |
}, | |
{ | |
"person_id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4", | |
"enemy_id": "a4adc3fd-485f-4fc7-a561-863ea78e9bb9", | |
"flavor": "Teacher" | |
}, | |
{ | |
"person_id": "5d7994d2-7960-4d73-9e1d-b4c255703765", | |
"enemy_id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4", | |
"flavor": "Occupational Rivalry" | |
} | |
]'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- cleanup if there's a people table | |
DROP TABLE IF EXISTS people; | |
-- good hygene | |
DROP VIEW IF EXISTS people; | |
-- now make the view | |
CREATE VIEW people AS | |
SELECT | |
id, | |
first_name, | |
last_name, | |
people_raw.characteristics || m.characteristics AS characteristics | |
FROM | |
people_raw, | |
LATERAL ( | |
SELECT | |
jsonb_build_object( | |
'enemies', | |
coalesce( | |
jsonb_agg(jsonb_build_object('id', enemy_id, 'flavor', flavor)), | |
'[]'::jsonb | |
) | |
) AS characteristics | |
FROM | |
enemy_association | |
WHERE person_id = people_raw.id | |
) AS m | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
last_name, | |
characteristics | |
FROM people | |
WHERE id='5d7994d2-7960-4d73-9e1d-b4c255703765'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{"enemies": | |
[ | |
{ | |
"id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4", | |
"flavor": "Occupational Rivalry" | |
} | |
] | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION enemy_association_fn() RETURNS TRIGGER AS $$ | |
DECLARE | |
enemy_ids UUID[] not null default ARRAY[]::uuid[]; | |
BEGIN | |
-- handle DELETE | |
IF (TG_OP = 'DELETE') THEN | |
-- handle deletion of a person | |
-- ... | |
END IF; -- IF (TG_OP = 'DELETE') | |
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN | |
-- validate that the `enemies` characteristic is an array | |
-- ... | |
END IF; -- IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') | |
-- handle INSERT | |
IF (TG_OP = 'INSERT') THEN | |
-- handle insertion of a new person | |
-- ... | |
END IF; -- IF (TG_OP = 'INSERT') | |
-- UPDATE is the most complex | |
IF (TG_OP = 'UPDATE' AND (OLD != NEW)) THEN | |
-- handle updating an existing person | |
-- ... | |
END IF; -- IF (TG_OP = 'UPDATE' AND (OLD != NEW)) | |
RETURN NEW; | |
END; | |
$$ LANGUAGE plpgsql; | |
DROP TRIGGER IF EXISTS people_enemy_change_tg ON people; | |
CREATE TRIGGER people_enemy_change_tg | |
INSTEAD OF | |
INSERT OR UPDATE OR DELETE ON people | |
FOR EACH ROW EXECUTE PROCEDURE enemy_association_fn(); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF (TG_OP = 'DELETE') THEN | |
RAISE NOTICE 'DELETING % in people_raw (%)', TG_WHEN, OLD; | |
DELETE FROM | |
people_raw | |
WHERE | |
id=OLD.id; | |
return OLD; | |
END IF; -- IF (TG_OP = 'DELETE') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN | |
-- only if there's an `enemies` characteristic | |
IF (NEW.characteristics ? 'enemies') THEN | |
IF (jsonb_typeof(NEW.characteristics->'enemies') != 'array') THEN | |
RAISE EXCEPTION | |
'characteristics->''enemies'' must contain an array: % not allowed', | |
NEW.characteristics->'enemies'; | |
END IF; | |
END IF; | |
END IF; -- IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF (TG_OP = 'INSERT') THEN | |
RAISE NOTICE 'INSERT people_raw(%)', NEW; | |
-- save the new person - *before* we try to refer to it | |
RAISE NOTICE 'actually INSERT people_raw(%)', NEW; | |
IF (NEW.id) THEN | |
-- an id value was given, use it | |
INSERT INTO people_raw | |
( | |
id, | |
last_name, | |
first_name, | |
characteristics | |
) | |
VALUES | |
( | |
NEW.id::uuid, | |
NEW.last_name::text, | |
NEW.first_name::text, | |
(NEW.characteristics - 'enemies')::jsonb | |
); | |
ELSE | |
-- let id default | |
INSERT INTO people_raw | |
( | |
last_name, | |
first_name, | |
characteristics | |
) | |
VALUES | |
( | |
NEW.last_name::text, | |
NEW.first_name::text, | |
(NEW.characteristics - 'enemies')::jsonb | |
); | |
END IF; | |
-- only if there's an `enemies` characteristic | |
IF (NEW.characteristics ? 'enemies') THEN | |
INSERT INTO enemy_association | |
SELECT | |
NEW.id AS person_id, | |
(enemy->>'id')::uuid AS enemy_id, | |
enemy->'flavor' AS flavor | |
FROM jsonb_array_elements(NEW.characteristics->'enemies') AS enemy; | |
END IF; | |
END IF; -- IF (TG_OP = 'INSERT') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- UPDATE is the most complex | |
IF (TG_OP = 'UPDATE' AND (OLD != NEW)) THEN | |
RAISE NOTICE 'UPDATE in people_raw(%, %)', OLD, NEW; | |
enemy_ids = ARRAY[]::uuid[]; | |
-- update the person with `enemies` removed | |
UPDATE people_raw | |
SET ( | |
last_name, | |
first_name, | |
characteristics | |
) = ( | |
NEW.last_name::text, | |
NEW.first_name::text, | |
(NEW.characteristics - 'enemies')::jsonb | |
) | |
WHERE id=NEW.id; | |
-- if there is a new `enemies` characteristic | |
IF (NEW.characteristics ? 'enemies') THEN | |
-- populate enemy_ids with the OLD enemies list (or an empty array) | |
IF (OLD.characteristics ? 'enemies') THEN | |
SELECT | |
coalesce(array_agg((enemy->>'id')::UUID), ARRAY[]::uuid[]) | |
INTO STRICT | |
enemy_ids | |
FROM | |
jsonb_array_elements(OLD.characteristics->'enemies') AS enemy; | |
END IF; | |
RAISE NOTICE 'old enemy_ids: %', enemy_ids; | |
-- insert all of the NEW enemies that are not in the OLD enemies list | |
INSERT INTO enemy_association | |
SELECT | |
NEW.id AS person_id, | |
(enemy->>'id')::uuid AS enemy_id, | |
enemy->'flavor' AS flavor | |
FROM | |
jsonb_array_elements(NEW.characteristics->'enemies') AS enemy | |
WHERE | |
NOT (ARRAY[(enemy->>'id')::uuid] <@ enemy_ids); | |
-- populate enemy_ids with the NEW enemies list (or an empty array) | |
SELECT | |
coalesce(array_agg((enemy->>'id')::UUID), ARRAY[]::uuid[]) | |
INTO STRICT | |
enemy_ids | |
FROM | |
jsonb_array_elements(NEW.characteristics->'enemies') AS enemy; | |
RAISE NOTICE 'new enemy_ids: %', enemy_ids; | |
-- remove all OLD enemies that are not also in NEW enemies | |
IF (OLD.characteristics ? 'enemies') THEN | |
DELETE FROM | |
enemy_association | |
USING | |
jsonb_array_elements(OLD.characteristics->'enemies') AS enemy | |
WHERE | |
person_id=OLD.id | |
AND enemy_id=(enemy->>'id')::uuid | |
AND NOT (ARRAY[(enemy->>'id')::uuid] <@ enemy_ids); | |
END IF; | |
-- ends IF (NEW.characteristics ? 'enemies') | |
-- otherwise, if there WAS an `enemies` characteristic, clean up | |
ELSIF (OLD.characteristics ? 'enemies') THEN | |
RAISE NOTICE 'removing old enemies'; | |
DELETE FROM | |
enemy_association | |
WHERE | |
person_id=OLD.id; | |
END IF; | |
END IF; -- IF (TG_OP = 'UPDATE' AND (OLD != NEW)) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Add the complex relationship that is Jerry and Newman | |
-- Note: It appears that Newman doesn't have a known first name, and Newman is actually his last name. | |
-- Delay the checking of the constraints until the end of the transaction for the following queries | |
SET CONSTRAINTS ALL DEFERRED; | |
INSERT INTO people | |
(id, last_name, first_name, characteristics) | |
SELECT id::uuid, last_name, first_name, characteristics | |
FROM json_populate_recordset (NULL::people, ' | |
[ | |
{ | |
"id": "0b72b205-85e5-4068-a323-4071e9b9aa5d", | |
"last_name": "Mr.", | |
"first_name": "Newman", | |
"characteristics": { | |
"enemies": [{"id": "5680f19e-82cf-4ce0-bb45-8ae7774efa59", "flavor": "Personality Conflict"}] | |
} | |
}, | |
{ | |
"id": "5680f19e-82cf-4ce0-bb45-8ae7774efa59", | |
"last_name": "Jerry", | |
"first_name": "Seinfeld", | |
"characteristics": { | |
"enemies": [{"id": "0b72b205-85e5-4068-a323-4071e9b9aa5d", "flavor": "Personality Conflict"}] | |
} | |
} | |
]'); | |
-- Joe Davola is Jerry and Newman's mutual enemy, and they are his enemies | |
INSERT INTO enemy_association | |
SELECT person_id, enemy_id, flavor | |
FROM json_populate_recordset (NULL::enemy_association, ' | |
[ | |
{ | |
"person_id": "0b72b205-85e5-4068-a323-4071e9b9aa5d", | |
"enemy_id": "5d7994d2-7960-4d73-9e1d-b4c255703765", | |
"flavor": "Unexplained" | |
}, | |
{ | |
"person_id": "5d7994d2-7960-4d73-9e1d-b4c255703765", | |
"enemy_id": "0b72b205-85e5-4068-a323-4071e9b9aa5d", | |
"flavor": "Association with Kramer" | |
}, | |
{ | |
"person_id": "5680f19e-82cf-4ce0-bb45-8ae7774efa59", | |
"enemy_id": "5d7994d2-7960-4d73-9e1d-b4c255703765", | |
"flavor": "Unexplained" | |
}, | |
{ | |
"person_id": "5d7994d2-7960-4d73-9e1d-b4c255703765", | |
"enemy_id": "5680f19e-82cf-4ce0-bb45-8ae7774efa59", | |
"flavor": "Professional Rivalry" | |
} | |
]'); | |
-- Joe is also Kramer's enemy, even though he doesn't know why | |
-- In this case we'll manipulate the `people` JSON directly | |
UPDATE people | |
SET characteristics = ( | |
characteristics || jsonb_build_object( | |
'enemies', | |
to_jsonb( | |
ARRAY [ | |
jsonb_build_object( | |
'id', '5d7994d2-7960-4d73-9e1d-b4c255703765', | |
'flavor', 'Unexplained' | |
) | |
]::jsonb[] | |
) | |
) | |
) | |
WHERE id = '6ae1da27-0565-4649-96c5-e4c1da48de21'; | |
-- Delete Jerry and Newman - mututal destruction, perhaps? | |
-- We never did see that 2020 New Year's Party, after all. | |
-- We need the deferred checks to still be set, BTW | |
DELETE FROM people WHERE id IN ( | |
'0b72b205-85e5-4068-a323-4071e9b9aa5d', | |
'5680f19e-82cf-4ce0-bb45-8ae7774efa59' | |
); | |
-- Now all of the relevant entries in `people_raw` and `enemy_association` for Jerry and Newman have been removed. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION enemy_association_fn() RETURNS TRIGGER AS $$ | |
DECLARE | |
enemy_ids UUID[] not null default ARRAY[]::uuid[]; | |
BEGIN | |
-- handle DELETE | |
IF (TG_OP = 'DELETE') THEN | |
RAISE NOTICE 'DELETING % in people_raw (%)', TG_WHEN, OLD; | |
DELETE FROM | |
people_raw | |
WHERE | |
id=OLD.id; | |
return OLD; | |
END IF; -- IF (TG_OP = 'DELETE') | |
-- handle INSERT or UPDATE (shared code only) | |
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN | |
-- only if there's an `enemies` characteristic | |
IF (NEW.characteristics ? 'enemies') THEN | |
IF (jsonb_typeof(NEW.characteristics->'enemies') != 'array') THEN | |
RAISE EXCEPTION | |
'characteristics->''enemies'' must contain an array: % not allowed', | |
NEW.characteristics->'enemies'; | |
END IF; | |
END IF; | |
END IF; -- IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') | |
-- handle INSERT | |
IF (TG_OP = 'INSERT') THEN | |
RAISE NOTICE 'INSERT people_raw(%)', NEW; | |
-- save the new person - *before* we try to refer to it | |
RAISE NOTICE 'actually INSERT people_raw(%)', NEW; | |
IF (NEW.id) THEN | |
-- an id value was given, use it | |
INSERT INTO people_raw | |
( | |
id, | |
last_name, | |
first_name, | |
characteristics | |
) | |
VALUES | |
( | |
NEW.id::uuid, | |
NEW.last_name::text, | |
NEW.first_name::text, | |
(NEW.characteristics - 'enemies')::jsonb | |
); | |
ELSE | |
-- let id default | |
INSERT INTO people_raw | |
( | |
last_name, | |
first_name, | |
characteristics | |
) | |
VALUES | |
( | |
NEW.last_name::text, | |
NEW.first_name::text, | |
(NEW.characteristics - 'enemies')::jsonb | |
); | |
END IF; | |
-- only if there's an `enemies` characteristic | |
IF (NEW.characteristics ? 'enemies') THEN | |
INSERT INTO enemy_association | |
SELECT | |
NEW.id AS person_id, | |
(enemy->>'id')::uuid AS enemy_id, | |
enemy->'flavor' AS flavor | |
FROM jsonb_array_elements(NEW.characteristics->'enemies') AS enemy; | |
END IF; | |
END IF; -- IF (TG_OP = 'INSERT') | |
-- handle UPDATE | |
-- UPDATE is the most complex | |
IF (TG_OP = 'UPDATE' AND (OLD != NEW)) THEN | |
RAISE NOTICE 'UPDATE in people_raw(%, %)', OLD, NEW; | |
enemy_ids = ARRAY[]::uuid[]; | |
-- update the person with `enemies` removed | |
UPDATE people_raw | |
SET ( | |
last_name, | |
first_name, | |
characteristics | |
) = ( | |
NEW.last_name::text, | |
NEW.first_name::text, | |
(NEW.characteristics - 'enemies')::jsonb | |
) | |
WHERE id=NEW.id; | |
-- if there is a new `enemies` characteristic | |
IF (NEW.characteristics ? 'enemies') THEN | |
-- populate enemy_ids with the OLD enemies list (or an empty array) | |
IF (OLD.characteristics ? 'enemies') THEN | |
SELECT | |
coalesce(array_agg((enemy->>'id')::UUID), ARRAY[]::uuid[]) | |
INTO STRICT | |
enemy_ids | |
FROM | |
jsonb_array_elements(OLD.characteristics->'enemies') AS enemy; | |
END IF; | |
RAISE NOTICE 'old enemy_ids: %', enemy_ids; | |
-- insert all of the NEW enemies that are not in the OLD enemies list | |
INSERT INTO enemy_association | |
SELECT | |
NEW.id AS person_id, | |
(enemy->>'id')::uuid AS enemy_id, | |
enemy->'flavor' AS flavor | |
FROM | |
jsonb_array_elements(NEW.characteristics->'enemies') AS enemy | |
WHERE | |
NOT (ARRAY[(enemy->>'id')::uuid] <@ enemy_ids); | |
-- populate enemy_ids with the NEW enemies list (or an empty array) | |
SELECT | |
coalesce(array_agg((enemy->>'id')::UUID), ARRAY[]::uuid[]) | |
INTO STRICT | |
enemy_ids | |
FROM | |
jsonb_array_elements(NEW.characteristics->'enemies') AS enemy; | |
RAISE NOTICE 'new enemy_ids: %', enemy_ids; | |
-- remove all OLD enemies that are not also in NEW enemies | |
IF (OLD.characteristics ? 'enemies') THEN | |
DELETE FROM | |
enemy_association | |
USING | |
jsonb_array_elements(OLD.characteristics->'enemies') AS enemy | |
WHERE | |
person_id=OLD.id | |
AND enemy_id=(enemy->>'id')::uuid | |
AND NOT (ARRAY[(enemy->>'id')::uuid] <@ enemy_ids); | |
END IF; | |
-- ends IF (NEW.characteristics ? 'enemies') | |
-- otherwise, if there WAS an `enemies` characteristic, clean up | |
ELSIF (OLD.characteristics ? 'enemies') THEN | |
RAISE NOTICE 'removing old enemies'; | |
DELETE FROM | |
enemy_association | |
WHERE | |
person_id=OLD.id; | |
END IF; | |
END IF; -- IF (TG_OP = 'UPDATE' AND (OLD != NEW)) | |
RETURN NEW; | |
END; | |
$$ LANGUAGE plpgsql; | |
DROP TRIGGER IF EXISTS people_enemy_change_tg ON people; | |
CREATE TRIGGER people_enemy_change_tg | |
INSTEAD OF | |
INSERT OR UPDATE OR DELETE ON people | |
FOR EACH ROW EXECUTE PROCEDURE enemy_association_fn(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment