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.
answerand reference theusers_idandquestion_id, how can I store the actual answer in a column?