Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE example (
  example_id     serial PRIMARY KEY
, totally_unique jsonb NOT NULL  -- NOT json
);

CREATE TABLE example_key (
  key   text
, value text
, PRIMARY KEY (key, value)
);

CREATE OR REPLACE FUNCTION trg_example_insupdelbef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- split UPDATE into DELETE & INSERT to simplify
   IF TG_OP = 'UPDATE' THEN
      -- Works in pg 9.4+ with jsonb:
      -- Does not work with json since there is no "=" operator for json
      IF OLD.totally_unique IS NOT DISTINCT FROM NEW.totally_unique THEN
         RETURN NEW;  -- exit, nothing to do
      END IF;
   END IF;

   IF TG_OP IN ('DELETE', 'UPDATE') THEN
      DELETE FROM example_key k
      USING  jsonb_each_text(OLD.totally_unique) j(key, value)
      WHERE  j.key = k.key
      AND    j.value = k.value;

      IF TG_OP = 'DELETE' THEN
         RETURN OLD;  -- exit, we are done
      END IF;
   END IF;

   INSERT INTO example_key(key, value)
   SELECT *
   FROM   jsonb_each_text(NEW.totally_unique) j;

   RETURN NEW;
END
$func$;

CREATE TRIGGER example_insupdelbef
BEFORE INSERT OR DELETE OR UPDATE OF totally_unique ON example
FOR EACH ROW EXECUTE PROCEDURE trg_example_insupdelbef();

CREATE TABLE

CREATE TABLE

CREATE FUNCTION

CREATE TRIGGER

-- works
INSERT INTO example(example_id, totally_unique) VALUES
  (1, '{"a": 1, "b": 2}')
, (2, '{"a": 2, "b": 3}')
;

INSERT 0 2

-- fails!
INSERT INTO example(totally_unique)
VALUES ('{"a": 2, "b": 7}');

ERROR:  duplicate key value violates unique constraint "example_key_pkey"
DETAIL:  Key (key, value)=(a, 2) already exists.
CONTEXT:  SQL statement "INSERT INTO example_key(key, value)
   SELECT *
   FROM   jsonb_each_text(NEW.totally_unique) j"
PL/pgSQL function trg_example_insupdelbef() line 23 at SQL statement

-- fails!
INSERT INTO example(totally_unique)
VALUES ('{"a": 7, "b": 2}');

ERROR:  duplicate key value violates unique constraint "example_key_pkey"
DETAIL:  Key (key, value)=(b, 2) already exists.
CONTEXT:  SQL statement "INSERT INTO example_key(key, value)
   SELECT *
   FROM   jsonb_each_text(NEW.totally_unique) j"
PL/pgSQL function trg_example_insupdelbef() line 23 at SQL statement

TABLE example;
TABLE example_key;

example_id
totally_unique

1
{"a": 1, "b": 2}

2
{"a": 2, "b": 3}

SELECT 2

key
value

a
1

b
2

a
2

b
3

SELECT 4

DELETE FROM example WHERE example_id = 2;

-- works
INSERT INTO example(example_id, totally_unique)
VALUES (3, '{"a": 2, "b": 7}');

TABLE example;
TABLE example_key;

DELETE 1

INSERT 0 1

example_id
totally_unique

1
{"a": 1, "b": 2}

3
{"a": 2, "b": 7}

SELECT 2

key
value

a
1

b
2

a
2

b
7

SELECT 4

UPDATE example
SET    totally_unique = '{"a": 22, "b": 77}'
WHERE  example_id = 3;

TABLE example;
TABLE example_key;

UPDATE 1

example_id
totally_unique

1
{"a": 1, "b": 2}

3
{"a": 22, "b": 77}

SELECT 2

key
value

a
1

b
2

a
22

b
77

SELECT 4

-- fails
UPDATE example
SET    totally_unique = '{"a": 1, "b": 77}'
WHERE  example_id = 3;

ERROR:  duplicate key value violates unique constraint "example_key_pkey"
DETAIL:  Key (key, value)=(a, 1) already exists.
CONTEXT:  SQL statement "INSERT INTO example_key(key, value)
   SELECT *
   FROM   jsonb_each_text(NEW.totally_unique) j"
PL/pgSQL function trg_example_insupdelbef() line 23 at SQL statement