如何查询 JSONB 字段字典中的值数组

Question

我有一个jsonb列，其中包含一个字典，该字典具有指向字符串值数组的键。我需要查询该数组。

该表（称为"事物"）如下所示：

------------------
| my_column      |
|----------------|
| { "a": ["X"] } |
------------------

我需要编写两个查询：

数组是否包含值"X"？

数组不包含值"X"吗？

my_column具有非 null 约束，但它可以包含空字典。字典还可以包含其他键/值对。

第一个查询很简单：

SELECT * FROM things
  WHERE my_column -> 'a' ? 'X';`

第二个被证明更具挑战性。我从那里开始：

SELECT * FROM things
  WHERE NOT my_column -> 'a' ? 'X';

。但这排除了所有具有不包含关键'a'的词典的记录。所以我像这样修改它：

SELECT * FROM things
  WHERE my_column -> 'a' IS NULL OR NOT
        my_column -> 'a' ? 'X';

这有效，但有更好的方法吗？另外，是否可以为此查询编制索引，如果是，如何？

Answer 1

我不确定是否有更好的方法 - 老实说，这对我来说看起来很简单。

至于索引，您可以做几件事。

首先，您可以为 jsonb 字段编制索引。在该字段上放置 GIN 索引应该有助于使用"存在"类型的运算符（如 ? ）。

如果出于某种原因这不是您想要的解决方案，Postgres 支持函数索引和部分索引。功能索引可能如下所示：

CREATE INDEX ON things ( my_column -> 'a' );

（注意：看起来 postgres 在该语法方面遇到了问题，这可能是一个错误。不过，这个概念成立。

部分索引会变得更加具体，甚至可能看起来像：

CREATE INDEX ON things (my_column)
  WHERE my_column -> 'a' IS NULL OR NOT
    my_column -> 'a' ? 'X';

显然，这对更一般的查询没有帮助。

猜测一下，使用 GIN 索引索引整个列是正确的方法，或者至少是正确的起点。