Tablica zapytań PostgreSQL JSON względem wielu wartości

Chcę napisać zapytanie do jsonbtypu w Postgres, które podając tablicę identyfikatorów klientów znajdzie odpowiednie grupy.

Biorąc pod uwagę tę przykładową tabelę:

CREATE TABLE grp(d JSONB NOT NULL);

INSERT INTO grp VALUES
   ('{"name":"First","arr":["foo"], "customers":[{"id":"1", "name":"one"},{"id":"2", "name":"two"}]}')
 , ('{"name":"Second","arr":["foo","bar"], "customers":[{"id":"3", "name":"three"},{"id":"4", "name":"four"}]}')
 , ('{"name":"Third","arr":["bar","baz"], "customers":[{"id":"5", "name":"five"},{"id":"6", "name":"seven"}]}');

Znalazłem podobne pytanie ( PostgreSql JSONB SELECT względem wielu wartości ) i udało mi się osiągnąć to, co chcę na prostej tablicy za pomocą tego zapytania:

SELECT d FROM grp WHERE d->'arr' ?| ARRAY['foo', 'bar'];

Nie mogę jednak sprawić, aby działał, gdy tablica zawiera obiekty JSON :

SELECT d FROM grp WHERE d->'customers' ?| ARRAY['{"id":"1"}', '{"id":"5"}'];

Oto, czego oczekuję od mojego zapytania:

grp „Pierwszy” -> klient „1”

grp „Trzeci” -> klient „5”

— BartZ
źródło

Istnieje sposób: połączyć operator przechowujący@> z ANYkonstrukcją :

SELECT d
FROM   grp
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

Lub:

...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

Konieczne jest jsonb[]jawne rzutowanie tablicy . I zauważ, że każdy element jest tablicą JSON wewnątrz, jak @>wymaga tego operator . Jest to tablica tablic JSON.

Możesz użyć do tego indeksu:

Indeks znajdujący element w tablicy JSON

Instrukcja wyraźnie stwierdza , że operator ?|jest dla ciągów tylko.

— Erwin Brandstetter
źródło