Подтвердить что ты не робот

Запрос внутри массивов Postgres JSON

Как бы вы искали поиск элемента внутри массива, хранящегося в столбце json? (Обновление: см. Также обновленный ответ 9.4 для столбцов jsonb.)

Если у меня есть такой JSON-документ, который хранится в столбце json с именем blob:

{"name": "Wolf",
 "ids": [185603363281305602,185603363289694211]}

то, что я хотел бы сделать, это что-то вроде:

SELECT * from "mytable" WHERE 185603363289694211 = ANY("blob"->'ids');

и вывести все соответствующие строки. Но это не работает, потому что "blob"->'ids' возвращает значения JSON, а не массивы Postgres.

Я хотел бы также создать индекс для отдельных идентификаторов, если это возможно.

Ответ 1

Следующий оригинальный ответ применяется только к Postgres 9.3. Для ответа Postgres 9.4 см. Обновление ниже.

Это основывается на ответах на Erwin, но немного более явственно относится к этому вопросу.

Идентификаторы в этом случае bigint s, поэтому создайте вспомогательную функцию для преобразования массива JSON в массив Postgres bigint:

CREATE OR REPLACE FUNCTION json_array_bigint(_j json)
  RETURNS bigint[] AS
$$
SELECT array_agg(elem::text::bigint)
FROM json_array_elements(_j) AS elem
$$
  LANGUAGE sql IMMUTABLE;

Мы могли бы просто легко (и, возможно, более повторно использовать) вместо этого вернуть массив text. Я подозреваю, что индексирование на bigint намного быстрее, чем text, но мне сложно найти доказательства онлайн, чтобы поддержать это.

Для построения индекса:

CREATE INDEX "myindex" ON "mytable" 
  USING GIN (json_array_bigint("blob"->'ids'));

Для запросов это работает и использует индекс:

SELECT * FROM "mytable" 
  WHERE '{185603363289694211}' <@ json_array_bigint("blob"->'ids');

Выполнение этого также будет работать для запросов, но оно не использует индекс:

SELECT * FROM "mytable" 
  WHERE 185603363289694211 = ANY(json_array_bigint("blob"->'ids'));

Обновление для 9.4

Postgres 9.4 представил тип jsonb. Это хороший ответ SO о jsonb и когда вы должны использовать его поверх json. Короче говоря, если вы когда-либо запрашиваете JSON, вы должны использовать jsonb.

Если вы построите свой столбец как jsonb, вы можете использовать этот запрос:

SELECT * FROM "mytable"
  WHERE blob @> '{"ids": [185603363289694211]}';

@> is Postgres 'содержит оператор задокументирован для jsonb здесь. Спасибо Алену ответ, чтобы привлечь внимание к этому.

Ответ 2

Сначала попробуйте оператор ->> вместо ->, чтобы удалить слой JSON из значения массива.

Далее запрос может работать следующим образом: Как запросить использование полей внутри нового типа данных PostgreSQL JSON?

И индексирование может работать следующим образом:
Указатель для поиска элемента в массиве JSON

Ответ 3

Я знаю, что прошло какое-то время...

В postgresql-9.5 теперь можно легко запросить его.

select '{"name": "Wolf",
         "ids": [185603363281305602,185603363289694211]}'::jsonb
       @> '{"ids":[185603363281305602]}'

Я думаю, вы должны использовать поле jsonb вместо этого, и впоследствии вы можете индексировать его.

CREATE INDEX idx_gin_ids ON mytable USING gin ((blob -> 'ids'));