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

Question

我有一个带有JSONB列myJsonb的表myTable，其数据结构我想像这样索引：

{
  "myArray": [
    {
      "subItem": {
        "email": "[e-mail]"
      }
    },
    {
      "subItem": {
        "email": "[e-mail]"
      }
    }
  ]
}

我想在电子邮件上运行索引查询，如：

SELECT *
FROM mytable
WHERE '[e-mail]' IN (
  SELECT lower(
      jsonb_array_elements(myjsonb -> 'myArray')
      -> 'subItem'
      ->> 'email'
  )
);

我如何为此创建Postgres JSONB索引？

Answer 1

如果其中不需要lower（），则查询可以简单高效：

SELECT *
FROM   mytable
WHERE  myjsonb -> 'myArray' @> '[{"subItem": {"email": "[e-mail]"}}]'

由索引支持：

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}') = '[e-mail]'
   );

但是我想不出一个好的方法来使用索引。

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

功能：

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') @> '"[e-mail]"';

虽然这适用于未类型化的字符串文字或实际的jsonb值，但如果您传递text或varchar（如在准备好的语句中），它将停止工作。Postgres不知道如何强制转换，因为输入是模棱两可的。在这种情况下，您需要显式强制转换：

... @> '"[e-mail]"'::text::jsonb;

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

... @> to_jsonb('[e-mail]'::text);

相关，有更多解释：

• 查询JSON类型内的数组元素
• 用于在JSON数组中查找元素的索引