Блог

Я готовлюсь использовать хранилище json PostgreSQL и функциональность запросов. Я думаю, что я немного понимаю часть вставки и запроса, но я не могу найти пример того, как поддерживать (через индекс) запрос в json path глубиной более одного уровня. Я тестирую со следующим:

 CREATE TABLE orders (
    id serial NOT NULL PRIMARY KEY,
    info json NOT NULL
);

INSERT INTO orders (info)
VALUES('{ "customer": "Mark Stevens", "items": {"product": { "name" : "Phone","qty": 3}}}'),
      ('{ "customer": "Josh William", "items": {"product": { "name" : "Toy Car","qty": 1}}}'),
      ('{ "customer": "Mary Clark", "items": {"product": { "name" : "Toy Train","qty": 2}}}');
  

Это немного сконструированный пример. Суть в том, что поле qty вложено глубиной более одного уровня.

Я могу выполнить запрос по этому запросу для всех записей с кол-вом 1:

 SELECT * FROM orders
WHERE info::jsonb @@ '$.items.product.qty == 1';
  

Все это работает нормально. Протестировано с использованием pgAdmin.
Итак, теперь я хочу определить индекс для поддержки этого запроса (или его версию, которая может поддерживаться индексом, поскольку часто имеет значение способ написания запроса).

Я просматривал здесь и в документации pg, но не увидел примера определения индекса, которое я мог бы превратить в рабочее для этого примера. Кажется, что все примеры охватывают пути только одного уровня. например, если бы кол-во было на один уровень глубже в json, поддерживающий индекс выглядел бы примерно так

 CREATE INDEX orders_index ON orders (((info ->> 'customer')::VARCHAR), ((info #>> '{items, qty}')::INTEGER));
  

Итак, мой вопрос таков: возможно ли создать индекс, поддерживающий мой запрос на более глубоких уровнях json? И если да, может ли кто-нибудь привести мне пример?

Вы должны определить столбец jsonb , здесь нет никакого преимущества в использовании json . Следующее предполагает jsonb столбец, а не json колонку

Вы можете определить индекс GIN для столбца:

 create index on orders using gin (info);
  

Или, альтернативно, для немного меньшего и более эффективного индекса:

 create index on orders using gin (info jsonb_path_ops);
  

Затем запрос:

 SELECT * 
FROM orders
WHERE info @@ '$.items.product.qty == 1';
  

можно создать из этого индекса — с обычными ограничениями. Если у вас всего несколько сотен строк, это, вероятно, не будет использоваться.

Альтернативный запрос:

 SELECT * 
FROM orders
WHERE info @> '{"items": {"product" : {"qty": 1}}}'
  

также использовал бы этот индекс.

Вы можете создать меньший (GIN) индекс, если вы знаете, что всегда будете просматривать, например, products часть:

 create index on orders using gin ( (info #> '{items,products}') );
  

Но тогда вам нужно скорректировать свой запрос:

 SELECT * 
FROM orders
WHERE info #> '{items,products}' @@ '$.qty == 1';
  

Вы можете увидеть поддерживаемые операторы JSONB здесь

Если вы всегда хотите запрашивать количество, то, возможно, достаточно индекса B-дерева. Индексы B-дерева меньше индексов GIN, а также имеют меньшие накладные расходы на их обновление.

 create index on orders ( ((info #>> '{items,product,qty}')::int) );
  

Тогда следующий запрос будет использовать этот индекс:

 SELECT * 
FROM orders
WHERE (info #>> '{items,product,qty})::int = 1;