使用 JSONB 列内的值连接表

有两个表：

授权联系人（auth_contacts):

(
userid varchar
contacts jsonb
)

contacts包含具有属性的联系人数组{contact_id, type}

discussion:

(
contact_id varchar
discussion_id varchar
discussion_details jsonb
)

桌子auth_contacts至少有 100k 条记录，使其成为非 JSONB 类型是不合适的，因为它会使记录量增加一倍或三倍。

样本数据为auth_contacts:

userid  | contacts
'11111' | '{"contact": [{"type": "type_a", "contact_id": "1-A-12"}
                      , {"type": "type_b", "contact_id": "1-A-13"}]}'

discussion表有 500 万条奇数记录。

我想加入discussion.contact_id（关系列）带有联系人 ID，其中 json 对象数组内有一个 json 对象auth_contacts.contacts.

一种非常粗暴的方法是：

SELECT *
FROM discussion d 
JOIN (SELECT userid, JSONB_OBJECT_KEYS(a.contacts) AS auth_contact
      FROM auth_contacts a) AS contacts
      ON (d.contact_id = contacts.auth_contact::text)

它的作用实际上是在运行时创建（内部sql）用户ID与联系人ID表（这是我正在避免的，因此选择了JSONB数据类型 对具有大量记录的用户的查询需要 26 秒以上，这并不好。 尝试了其他几种方法：PostgreSQL 9.4：数组内 JSON 字段 id 的聚合/连接表

但应该有一种更干净、更好的方法，就像这样简单 加入d.contact_id = contacts -> contact -> contact_id?当我尝试这样做时，它不会产生任何结果。

当在网上搜索时，这似乎是一个相当麻烦的任务？

概念证明

你的“粗暴方式”实际上行不通。这是另一种粗略的方法：

SELECT *
FROM  auth_contacts a
    , jsonb_to_recordset(a.contacts->'contact') AS c(contact_id text)
JOIN  discussion d USING (contact_id);

正如已经评论过的，您还可以使用以下方式制定连接条件包含运算符@>:

SELECT *
FROM   auth_contacts a
JOIN   discussion d ON a.contacts->'contact'
                    @> json_build_array(json_build_object('contact_id', d.contact_id))::jsonb

而是使用 JSON 创建函数而不是字符串连接。看起来很麻烦，但如果有支持的话实际上会非常快功能性 jsonb_path_ops GIN index:

CREATE INDEX auth_contacts_contacts_gin_idx ON auth_contacts
USING  gin ((contacts->'contact') jsonb_path_ops);

Details:

• 用于在 JSON 数组中查找元素的索引
• Postgres 9.4 jsonb 数组作为表

正确的解决方案

这一切都很有趣，但这里的问题是关系模型。您的主张：

因此使其成为非 JSONB 类型是不合适的，因为它 将使记录数量增加一倍或三倍。

is 与正确的相反. It's nonsense将连接表所需的 ID 包装成 JSON 文档类型。使用多对多关系规范化您的表，并将您在数据库中使用的所有 ID 实现为具有适当数据类型的单独列。基本：

• 如何对 JSONB 类型的列执行更新操作
• 如何在PostgreSQL中实现多对多关系？