Skip to content

Instantly share code, notes, and snippets.

@giseburt

giseburt/JSON-in-postgresql-p3-01.sql Secret

Last active May 20, 2021
Embed
What would you like to do?
Managing Complex Data with JSON in PostgreSQL, part 3
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"
}
]');
-- 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
;
SELECT
last_name,
characteristics
FROM people
WHERE id='5d7994d2-7960-4d73-9e1d-b4c255703765';
{"enemies":
[
{
"id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4",
"flavor": "Occupational Rivalry"
}
]
}
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();
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')
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')
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')
-- 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))
-- 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.
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