How to enforce the shape of a JSON/JSONB in Postgres?-CodePudding

I am trying to store the response of questions of a survey in JSON, as they could be in boolean(Is this Bar? Yes/No), number(How much is Foo?), string(Describe what is Foo). It is working fine, but how can I enforce that for a certain question, the JSON will be of identical shape?

For example, for the question "How many Foo or Bar do you eat everyday?", I am expecting the following structure(let say it is column answer):

{
 foo: number,
 bar: number
}

How can I enforce that and keep my data consistent?

CodePudding user response：

There 'right' way yo do this is either to normalize your data or to enforce schema at the application level.

Postgres JSON is delibrately schemaless. The idea is that if your data is structured it should probably be in normal columns. However, JSON is a totally valid option in the situation where the data is structured but the schema is dynamic. In those situations it's best to enforce schema constraints at the application layer.

CodePudding user response：

In general, the topic is extensive and complicated. For those interested, I recommend the JSON Schema website.

For our purposes, we can use a very simple method of validation, limited to two issues, commented in the function body:

create or replace function validate_answer(answer jsonb, pattern jsonb)
returns bool language plpgsql as $$
declare
    rec record;
begin
-- does the answer contain exactly the same keys as the pattern?
    if not (
        select array_agg(keys_ans) = array_agg(keys_pat)
        from (
            select 
                jsonb_object_keys(answer) as keys_ans, 
                jsonb_object_keys(pattern) as keys_pat
            ) s
        ) then return false;
    end if;

-- are the value types of all keys the same in the answer and pattern?
    for rec in
        select *
        from jsonb_each(pattern)
    loop
        if jsonb_typeof(answer->rec.key) <> jsonb_typeof(rec.value)
        then return false;
        end if;
    end loop;
    return true;
end $$;

Test the function in Db<>Fiddle.