arrays - 在数组子对象上创建 Postgres JSONB 索引

Question

我有一个表 myTable，它有一个 JSONB 列 myJsonb，它的数据结构我想索引如下:

{
  "myArray": [
    {
      "subItem": {
        "email": "bar@bar.com"
      }
    },
    {
      "subItem": {
        "email": "foo@foo.com"
      }
    }
  ]
}

我想在 email 上运行索引查询，例如:

SELECT *
FROM mytable
WHERE 'foo@foo.com' IN (
  SELECT lower(
      jsonb_array_elements(myjsonb -> 'myArray')
      -> 'subItem'
      ->> 'email'
  )
);

如何为此创建 Postgres JSONB 索引？

Answer 1

如果你不需要 lower() ，查询可以简单高效:

SELECT *
FROM   mytable
WHERE  myjsonb -> 'myArray' @> '[{"subItem": {"email": "foo@foo.com"}}]'

由 jsonb_path_ops 索引支持:

CREATE INDEX mytable_myjsonb_gin_idx ON mytable
USING  gin ((myjsonb -> 'myArray') jsonb_path_ops);

但是匹配是区分大小写的。

不区分大小写!

如果您需要不分大小写地进行搜索，事情就会变得更加复杂。

您可以使用此查询，类似于您的原始查询:

SELECT *
FROM   t
WHERE  EXISTS (
   SELECT 1
   FROM   jsonb_array_elements(myjsonb -> 'myArray') arr
   WHERE  lower(arr #>>'{subItem, email}') = 'foo@foo.com'
   );

但我想不出为此使用索引的好方法。

相反，我会使用基于提取小写电子邮件数组的函数的表达式索引:

功能:

CREATE OR REPLACE FUNCTION f_jsonb_arr_lower(_j jsonb, VARIADIC _path text[])
  RETURNS jsonb LANGUAGE sql IMMUTABLE AS
'SELECT jsonb_agg(lower(elem #>> _path)) FROM jsonb_array_elements(_j) elem';

索引:

CREATE INDEX mytable_email_arr_idx ON mytable
USING  gin (f_jsonb_arr_lower(myjsonb -> 'myArray', 'subItem', 'email') jsonb_path_ops);

查询:

SELECT *
FROM   mytable 
WHERE  f_jsonb_arr_lower(myjsonb -> 'myArray', 'subItem', 'email') @> '"foo@foo.com"';

虽然这适用于未类型化的字符串文字 或实际的jsonb 值，但如果您传递text 或varchar，它就会停止工作 (就像在准备好的语句中一样)。 Postgres 不知道如何转换，因为输入不明确。在这种情况下你需要一个明确的转换:

... @> '"foo@foo.com"'::text<b>::jsonb</b>;

或者传递一个不包含双引号的简单字符串，并在 Postgres 中转换为 jsonb:

... @> <b>to_jsonb(</b>'foo@foo.com'::text<b>)</b>;

相关，有更多解释:

• Query for array elements inside JSON type
• Index for finding an element in a JSON array