Managing Complex Data with JSON in PostgreSQL, part 3

JSON-in-postgresql-p3-01.sql

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"
  }
]');

JSON-in-postgresql-p3-02.sql

-- 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
;

JSON-in-postgresql-p3-03.sql

SELECT
  last_name,
  characteristics
FROM people
WHERE id='5d7994d2-7960-4d73-9e1d-b4c255703765';

JSON-in-postgresql-p3-04.json

{"enemies":
  [
    {
      "id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4",
      "flavor": "Occupational Rivalry"
    }
  ]
}

JSON-in-postgresql-p3-05.sql

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();

JSON-in-postgresql-p3-06.sql

  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')

JSON-in-postgresql-p3-07.sql

  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')

JSON-in-postgresql-p3-08.sql

  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')

JSON-in-postgresql-p3-09.sql

  -- 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))

JSON-in-postgresql-p3-10.sql

-- 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.

JSON-in-postgresql-p3-11-COMPLETE-TRIGGER.sql

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();