使用JSONB列中的值连接表

Question

有两个表格：

授权联系人（auth_Contacts）：

(
userid varchar
contacts jsonb
)

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

讨论：

(
contact_id varchar
discussion_id varchar
discussion_details jsonb
)

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

auth_联系人的样本数据：

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

讨论表有500万奇数记录。

我想参加讨论。contact_id（关系列），带有contact id，在auth_contacts中的json对象数组中包含一个json对象。联系人。

一个非常粗糙的方法是：

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）userid vs contact id table（这是我一直在避免的，因此选择了JSONB数据类型）。对于一个有大量记录的用户，这个查询需要26秒，这并不都是好的。尝试了其他几种方法：PostgreSQL 9.4：数组内JSON字段id上的聚合/联接表

但是应该有一个更干净，更好的方法，这将是简单的JOINd.contact_id=联系人-

搜索网络时，这似乎是一项相当繁琐的任务？




Answer 1

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

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

如前所述，还可以使用contains操作符@

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索引，实际上会非常快：

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

细节：

• 用于在JSON数组中查找元素的索引

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

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

与正确的相反。将连接表所需的ID包装为JSON文档类型是无稽之谈。使用多对多关系规范化您的表，并将您在数据库中使用的所有ID实现为具有适当数据类型的单独列。基础知识：

• 如何在Postgres 9.4中对JSONB类型的列执行更新操作