chungtinhlakho chungtinhlakho -5 years ago 195
JSON Question

postgres force json datatype

When working with JSON datatype, is there a way to ensure the input JSON must have elements. I don't mean primary, I want the JSON that gets inserted to at least have the id and name element, it can have more but at the minimum the id and name must be there.


Answer Source

The function checks what you want:

create or replace function json_has_id_and_name(val json)
returns boolean language sql as $$
    select coalesce(
            select array['id', 'name'] <@ array_agg(key)
            from json_object_keys(val) key

select json_has_id_and_name('{"id":1, "name":"abc"}'), json_has_id_and_name('{"id":1}');

 json_has_id_and_name | json_has_id_and_name 
 t                    | f
(1 row) 

You can use it in a check constraint, e.g.:

create table my_table (
    id int primary key,
    jdata json check (json_has_id_and_name(jdata))

insert into my_table values (1, '{"id":1}');

ERROR:  new row for relation "my_table" violates check constraint "my_table_jdata_check"
DETAIL:  Failing row contains (1, {"id":1}).
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download