postgresql - 如何使字段默认为分配给 jsonb 字段的值？

Question

我有一张 table :

CREATE TABLE inbound ( -- broadcasts received from RTs
    id SERIAL primary key,
    ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    rt VARCHAR(10) NOT NULL,
    region region NOT NULL,
    channel channel NOT NULL,
    payload jsonb,
    messageid, -- denormalised
    messagetype -- denormalised
);

有效负载可能是:{"a":{"messageid":"ABC123"}} 或 {"b":{"messageid":"ABC123"}}.

我希望 messageid 字段始终包含负载对象的 messageid 字段的值，无论它是 a 还是b.

我希望messagetype 是a 或b 的enum 值，以反射(reflect)内容的有效载荷。

在 Postgres 中对 JSON 数据进行反规范化是正确的做法，还是我最好为数据设置某种形式的 View ？

Answer 1

不要保留负载。将字段保存在。假设此架构:

create type messagetype as enum('a','b');
create table inbound (
    messageid text,
    messagetype messagetype
)

当接收到负载时:

with p (payload) as ( values
    ('{"a":{"messageid":"ABC123"}}'::jsonb),
    ('{"b":{"messageid":"ABC123"}}')
)
insert into inbound (messagetype, messageid)
select key::messagetype, value ->> 'messageid'
from p, jsonb_each(payload)

在请求时:

select jsonb_build_object(messagetype, jsonb_build_object('messageid', messageid))
from inbound
;
       jsonb_build_object       
--------------------------------
 {"a": {"messageid": "ABC123"}}
 {"b": {"messageid": "ABC123"}}