本文介绍了为元素的存在和唯一性索引Postgresql JSONB数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个名为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数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-27 21:02