將 JSONB 列加入 Postgres 中的普通 PK 列

我正在嘗試通過 Postgres 中該 jsonb 欄位上的屬性將普通表連接到具有 jsonb 欄位的表。例子：

普通表：

create table Res
(
   id uuid default uuid_generate_v4() not null
       constraint res_pkey
           primary key
)
;

帶有 jsonb 的表：

create table res_rem_sent
(
   body jsonb not null,
   search tsvector,
   created_at timestamp with time zone default now(),
   id uuid default uuid_generate_v4() not null
       constraint res_rem_sent_pkey
           primary key
);

create index idx_res_rem_sent
   on res_rem_sent (body)
;

create index idx_search_res_rem_sent
   on res_rem_sent (search)
;

該body欄位可能如下所示：{"resId": <GUID>, ....}

我想將res表res_rem_sent加入resId到resid 等於給定 GUID id 的表中。

您可以使用以下查詢：

SELECT
   *
FROM
   res
   JOIN res_rem_sent ON res_rem_sent.body->>'resId' = text(res.id) ;

JSON 將其所有內容表示為文本，這就是您必須非常小心 PostgreSQL 如何將 UUID 轉換為文本的原因。

需要考慮的事項：

1. ->>操作員獲取 JSON 對象欄位作為text。
2. 在 JSON(B) 方面，您需要使用標準形式（如UUID Type中所述）表示 UUID，以便 UUID 類型到 JSON(B) 文本表示形式之間的轉換產生相同的結果。
3. body->>'resId'如果您希望查詢是高性能的，您需要在零件上至少有一個（功能）索引

CREATE INDEX idx_uuid_res_rem_sent ON res_rem_sent((body->>'resId')) ;

-- NOTE: the double parenthesis aren't optional 4. 如果您還有一個文本索引，您將獲得最佳性能Res.id：

CREATE INDEX idx_uuid_res ON Res(text(id)) ; 5. 如果不使用文本表示，而是使用 UUID 表示來完成比較和索引，則性能可能會得到提高：

(res_rem_sent.body->>'resId')::uuid = res.id

在這種情況下，如果轉換失敗，您將無法將數據插入到表中。

DBfiddle在這裡看這個場景的實際模擬

引用自：https://dba.stackexchange.com/questions/171202