PostgreSQL - поиск комбинации атрибутов в массиве json и проверка наличия атрибута

Question

Я последовал за ответом Эрвина Брандштеттера о поиске с использованием комбинаций атрибутов. У меня тоже есть похожая проблема, но с разницей.

Допустим, мои данные таковы:

CREATE TABLE tracks (id serial, data jsonb);

INSERT INTO tracks (id, data)  VALUES
  (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect", "awarded": "True"}]')
, (3, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect", "awarded": "True"},
        {"artist": "Simple Plan",    "title": "Perfect"} 
       ]')
, (4, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect",  "year": "2005"},
        {"artist": "Simple Plan",    "title": "Perfect", "awarded": "True"},
        {"artist": "Simple Plan",    "title": "Perfect"} 
       ]');

Я также использую ту же индексацию: gin (data jsonb_path_ops)

Когда я запрашиваю:

SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]';

это вернется 1, '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]'

И когда я запрашиваю:

SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Perfect"}]';

это вернется

(2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect", "awarded": "True"}]')
,(3, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect", "awarded": "True"},
        {"artist": "Simple Plan",    "title": "Perfect"} 
       ]')
,(4, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect",  "year": "2005"},
        {"artist": "Simple Plan",    "title": "Perfect", "awarded": "True"},
        {"artist": "Simple Plan",    "title": "Perfect"} 
       ]')

Теперь я хочу сделать что-то отличное от вышеупомянутой темы:

1. Хотите получить только часть json для каждого идентификатора, которая соответствует запросу, т.е. только элемент {"artist": "Simple Plan", "title": "Perfect"}

2. Хотите соответствовать только не награжденному титульному листу Чтобы сэкономить место, я храню только "награжденный": "True", если титул присуждается. В противном случае поле "награжден" отсутствует, что означает, что название не было присвоено.

Таким образом, вывод будет примерно таким:

(3, '[ {"artist": "Simple Plan",    "title": "Perfect"} 
       ]')
,(4, '[{"artist": "Simple Plan",    "title": "Perfect",  "year": "2005"},
        {"artist": "Simple Plan",    "title": "Perfect"} 
       ]')

Для этого я попытался последовать другому ответу Эрвина. Но проблема в том, что id=4 будет иметь две строки в результате, тогда как я хочу иметь одну запись для идентификатора.

Это достижимо?

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

SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Perfect"}]
LIMIT 20;

и затем на возвращенных результатах sql я проверяю, упоминается ли "награжденное" поле или нет. Если присутствует поле "наград", я игнорирую этот элемент. Я понимаю, что это не эффективный способ выполнения запросов. Кроме того, поскольку я игнорирую элементы как часть постобработки, я получаю меньшее количество элементов.

Окружающая среда: я использую postgreSQL 10.

Надеюсь теперь мой вопрос понятен.

Answer 1

Если вы хотите получить целые массивы json, достаточно использовать слово not:

select * 
from tracks
where not data @> '[{"awarded": "True"}]'

 id |                            data                            
----+------------------------------------------------------------
  1 | [{"title": "Welcome to My Life", "artist": "Simple Plan"}]
(1 row)

Если вы хотите получить элементы массива, вы должны раскрутить массивы с помощью jsonb_array_elements():

select id, jsonb_agg(element) as data
from tracks
cross join jsonb_array_elements(data) as element
where 
    element->>'artist' = 'Simple Plan'
    and element->>'title' = 'Perfect'
    and element->>'awarded' is distinct from 'True'
group by id
order by id 

 id |                                                      data                                                      
----+----------------------------------------------------------------------------------------------------------------
  3 | [{"title": "Perfect", "artist": "Simple Plan"}]
  4 | [{"year": "2005", "title": "Perfect", "artist": "Simple Plan"}, {"title": "Perfect", "artist": "Simple Plan"}]
(2 rows)

Обратите внимание, что вы должны использовать is distinct from вместо <> потому что выражение может быть null если элемент не содержит ключ awarded,