为元素的存在和唯一性索引Postgresql JSONB数组 [英] Indexing Postgresql JSONB arrays for element existence and unicity

问题描述

我有一个名为posts的Postgresql 11.8表，我想在其中定义JSONB类型的列slugs，该列将包含字符串数组，例如["my-first-post", "another-slug-for-my-first-post"].

I have a Postgresql 11.8 table named posts where I would like to define a column slugs of type JSONB, which would contain arrays of strings such as ["my-first-post", "another-slug-for-my-first-post"].

我可以使用?存在运算符:SELECT * FROM posts WHERE slugs ? 'some-slug'找到具有特定子句的帖子.

I can find a post having a specific slug using the ? existence operator: SELECT * FROM posts WHERE slugs ? 'some-slug'.

每个帖子预计只有少数几个，但是帖子的数量有望增长.

Each post is expected to only have a handful of slugs but the amount of posts is expected to grow.

考虑以上查询，其中some-slug可以是任何字符串:

Considering the above query where some-slug could be any string:

• 如何定义索引以进行合理的查询(不进行全表扫描)?
• 如何确保同一条弹头不能多次出现(在不同阵列中以及在不同阵列中)?

我主要是在寻找Postgresql 11的解决方案，但也希望了解将来版本中的解决方案.

I am primarily looking for a solution for Postgresql 11 but also would be interested to know solutions in future versions, if any.

该数据库在Rails 6.0应用程序中使用，因此我对Rails迁移语法也很感兴趣.

The database is used in a Rails 6.0 app so I am also interested by the Rails migration syntax.

推荐答案

您可以在jsonb列上使用常规GIN索引来支持?运算符.

You can support the ? operator with a normal GIN index on the jsonb column.

但是，您不能强制使用数组值的唯一性.

However, you cannot enforce uniqueness of array values.

尤其是如果您希望具有数据库约束，则应该不使用JSON为数据建模.使用带有多个表和外键的常规规范化数据模型，可以很容易地实现这种唯一性约束.

Particularly if you want to have database constraints, you should not model your data using JSON. Use a regular normalized data model with several tables and foreign keys, then it will be easy to implement such an uniqueness constraint.

这篇关于为元素的存在和唯一性索引Postgresql JSONB数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！