在Array子对象上创建Postgres JSONB索引 [英] Create Postgres JSONB Index on Array Sub-Object

问题描述

我有一个表 myTable ，其中包含一个JSONB列 myJsonb ，其数据结构我想索引如下： / p>

I have table myTable with a JSONB column myJsonb with a data structure that I want to index like:

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

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

I want to run indexed queries on email like:

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

如何为此创建Postgres JSONB索引？

How do I create a Postgres JSONB index for that?

推荐答案

如果你不需要 lower（），那么查询可以简单有效： / p>

If you don't need the lower() in there, the query can be simple and efficient:

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

由 jsonb_path_ops 索引支持：

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

但匹配区分大小写。

如果您需要搜索以匹配无视案例，事情会变得更加复杂。

If you need the search to match disregarding case, things get more complex.

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

You could use this query, similar to your original:

SELECT *
FROM   t
WHERE  EXISTS (
   SELECT 1
   FROM   jsonb_array_elements(myjsonb -> 'myArray') arr
   WHERE  lower(arr #>>'{subItem, email}') = '[e-mail]'
   );

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

But I can't think of a good way to use an index for this.

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

Instead, I would use an expression index based on a function extracting an array of lower-case emails:

功能：

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不知道如何投射，因为输入是模棱两可的。在这种情况下你需要一个明确的演员：

While this works with an untyped string literal or with actual jsonb values, it stops working if you pass text or varchar (like in a prepared statement). Postgres does not know how to cast because the input is ambiguous. You need an explicit cast in this case:

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

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

Or pass a simple string without enclosing double quotes and do the conversion to jsonb in Postgres:

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

相关，有更多解释：

• 查询JSON类型中的数组元素


• 用于在JSON数组中查找元素的索引

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

这篇关于在Array子对象上创建Postgres JSONB索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！