Postgres 9.6 - поле jsonb сложного запроса, состоящее из массива jsons

Question

Моя таблица выглядит примерно так:

CREATE TABLE myjsontable(data JSONB NOT NULL);

INSERT INTO myjsontable VALUES ('[{"score":20 ,"category": 10 }, {"score":100 ,"category": 100 },{"score":500 ,"category": 50 }]');

INSERT INTO myjsontable VALUES ('[{"score":1000 ,"category": 40 }, {"score":30 ,"category": 50 },{"score":6000 ,"category": 100 }]');

INSERT INTO myjsontable VALUES ('[{"score":10 ,"category": 1 }, {"score":123 ,"category": 40 },{"score":1000 ,"category": 50 }]');

CREATE INDEX ON myjsontable USING GIN(data);

Я хочу получить только те записи, которые имеют категорию X и баллы выше Y для конкретного json в массиве jsons

Все, что мне удалось сделать, это использовать содержимое так:

SELECT * FROM myjsontable WHERE data @> '[{ "category": 10}]';

Я хотел бы сделать что-то вроде для простого запроса (например, NoSQL..)

 SELECT * FROM myjsontable WHERE data @> '[{ "category": 10, "score": ">10" }]';

Есть ли способ сделать простой, но сложный, где предложения, как в примере, приведенном, когда мое поле представляет собой массив jsons?

Answer 1

Поскольку категория и оценка хранятся в виде объектов json в массиве json, сначала массив должен быть unnested, а затем с помощью функции доступа json сравнивать значения в предложении where. Необходимо преобразовать json в текст перед преобразованием в int:

SELECT t.* 
FROM myjsontable t
CROSS JOIN JSONB_ARRAY_ELEMENTS(data) x
WHERE (x.value->>'category')::INT = 10 
  AND (x.value->>'score')::INT > 10

Также можно написать этот запрос, используя EXISTS пункт. Эта формулировка является немного более явной в своем намерении:

SELECT * FROM myjsontable 
WHERE EXISTS (SELECT TRUE FROM JSONB_ARRAY_ELEMENTS(data) x
              WHERE (value->>'category')::INT = 10 
                AND (value->>'score')::INT > 10)