Создание индекса Postgres JSONB для подобъекта массива

Question

Создание индекса Postgres JSONB для подобъекта массива

У меня есть стол 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 для этого?

4

arrays json postgresql indexing jsonb

Источник

user237225 14 дек '17 в 21:22

1 ответ

Решение

Другие вопросы по тегам arrays json postgresql indexing jsonb

user939860 15 дек '17 в 04:03 2017-12-15 04:03 · Accepted Answer · 2017-12-15 04:03

Если вам не нужно 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::jsonb;

Или передайте простую строку без двойных кавычек и выполните преобразование в jsonb в Postgres:

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

Связанные с более подробным объяснением: