如何在Postgres中执行JSON/JSONB的形状？

Question

我试图在JSON中存储对调查问题的回答，因为它们可能在布尔值（这是栏？是/否），数字（foo？），字符串（描述什么是foo）。它运行良好，但是我该如何执行某个问题，JSON的形状相同？

例如，对于“您每天要吃多少个Foo或酒吧？”的问题， 我期望以下结构（假设它是列答案）：

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

我该如何执行并保持数据一致？

Answer 1

这样做的“正确”方法是使您的数据正常化或在应用程序级别执行模式。

Postgres JSON是毫无用处的。这个想法是，如果您的数据结构化，则可能应在普通列中。但是，在数据结构但架构是动态的情况下，JSON是一个完全有效的选择。在这种情况下，最好在应用程序层执行模式约束。

Answer 2

通常，这个话题是广泛而复杂的。对于那些有兴趣的人，我建议 json Schema 网站。

出于我们的目的，我们可以使用一种非常简单的验证方法，仅限于两个问题，在功能体系中发表了评论：

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 $;

在 db＆lt;＆gt; fiddle。

如您所见，该模式只是一个格式良好的答案的一个示例。为每个问题定义一个模式，为答案表创建一个触发器，然后在触发函数中使用上述函数来验证新的或修改的答案。