3

I am trying to store the response to 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's say it is column answer):

{
 "foo": "number",
 "bar": "number"
}

How can I enforce that and keep my data consistent?

3
  • 1
    Normalize your data model and don't store it in JSON but a proper table Commented May 26, 2022 at 20:25
  • The reason why I reach for JSON is because I have to deal with multiple data types for the answer. If I create a table for answer and reference the users_id and question_id, how can I store the actual answer in a column? Commented May 26, 2022 at 20:47
  • 2
    You may try this postgres-json-schema approach. Commented May 26, 2022 at 21:03

2 Answers 2

3

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.

Sign up to request clarification or add additional context in comments.

2 Comments

Well, enforce schema constraints at the application layer but what if data is shared b/w more than one application? Consistency?
Multiple applications should never share a database. That's service design 101 - services communicate via messages, not shared state.
2

In general, the topic is extensive and complex. 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.

As you can see, the pattern is just an example of a well-formatted answer. Define a pattern for each question, create a trigger for the answers table and use the above function inside the trigger function to verify new or modified answers.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.