Constrain json keys in Postgres

Question

I have tried to look for this online and I simply cannot find anything.

I have a column in a PostgreSQL database of the JSON type. I also have a table in the database that signifies valid key names can be used in that JSON object.

An example is if I wanted to JSON to look like: { "key":"value", "key2":"value" }, key and key2 would have to be entries in the valid keys table in order for this to be inserted, otherwise it would break the constraint. I am using these in a C# project so I could probably do the checking in the code which I will also do, but I also want to stop someone from just creating a row via pgAdmin.

Any ideas?

You are looking for a foreign key constraint which is not supported for JSON (or XML or hstore). Can't you just use a regular column with a foreign key defined? If the JSON only contains a single value I don't see the benefit of it anyway. — user330315
– user330315, Commented Nov 25, 2014 at 19:15
I edited to show more than one key, which is possible. The one key was just an example. — GBreen12
– GBreen12, Commented Nov 25, 2014 at 19:19
You can certainly do it, but you'll have to write a trigger. If you have v8 installed in your DB it wouldn't be that difficult. — Joe Love
– Joe Love, Commented Nov 25, 2014 at 20:42
Yeah I was thinking a trigger may be necessary, but what would that even look like? — GBreen12
– GBreen12, Commented Nov 25, 2014 at 21:47

Clément Prévost · Accepted Answer · 2014-11-26 01:30:18Z

Use a trigger on insert and update that RAISE an exception if the database is in an inconsistent state.

CREATE TABLE t1 (
    value json
);
CREATE TABLE t2 (
    valid_key text
);

CREATE FUNCTION check_t1_val_integrity() RETURNS trigger AS $func$
    DECLARE 
       valid_key_count integer;
    BEGIN
        -- Count keys from NEW, not present in t2
       SELECT 
         count(*) 
       FROM 
         json_each(NEW.value) AS v(key,value)
         LEFT JOIN t2 ON t2.valid_key = v.key
       WHERE
         t2.valid_key IS NULL
       INTO 
         valid_key_count;

        -- If we found some key not present in t2, then raise exception
        IF valid_key_count > 0 THEN
            RAISE EXCEPTION 'value not valid';
        END IF;

        RETURN NEW;
    END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER check_t1_val_integrity_trigger BEFORE INSERT OR UPDATE ON t1
    FOR EACH ROW EXECUTE PROCEDURE check_t1_val_integrity();

Here is your tests:

INSERT INTO t2 (valid_key) VALUES(('a', ('b'));

-- should work well 
INSERT INTO t1 (value) VALUES(
  ('{"a":"a"}'::json, ('{"b":"b"}'::json), ('{"a":"a","b":"b"}'::json)
);

-- should raise exception 
INSERT INTO t1 (value) VALUES(
  ('{"a":"a", "c":"c"}'::json
);

Collectives™ on Stack Overflow

Constrain json keys in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related