Postgresql Abfrage Array von Objekten im JSONB-Feld

Gibt es keine einzige operation, die dir helfen können, aber Sie haben ein paar Optionen:

1. Wenn Sie eine kleine (und festen) Anzahl von ids zur Abfrage verwenden, können Sie mehrere kapselungs - @> kombiniert mit or; f.ex.:

where data @> '[{"id": "1884595530"}]' or data @> '[{"id": "791712670"}]'

Einem einfachen gin index kann Ihnen helfen, auf Ihre Daten-Spalte hier.

2. Wenn Sie eine variable Anzahl von ids (oder Sie haben eine Menge von Ihnen), die Sie verwenden können json[b]_array_elements() zu extrahieren jedes element des Arrays, der Aufbau einer id-Liste, und klicken Sie dann Fragen Sie ihn mit dem any-kapselungsoperator ?|:

select *
from   jsonbtest
where  to_json(array(select jsonb_array_elements(data) ->> 'id'))::jsonb ?|
         array['1884595530', '791712670'];

Leider können Sie index-Ausdruck, welcher eine sub-Abfrage. Wenn Sie indizieren möchten, müssen Sie eine Funktion erstellen für Sie:

create function idlist_jsonb(jsonbtest)
  returns jsonb
  language sql
  strict
  immutable
as $func$
  select to_json(array(select jsonb_array_elements($1.data) ->> 'id'))::jsonb
$func$;

create index on jsonbtest using gin (idlist_jsonb(jsonbtest));

Danach können Sie query-ids wie diese:

select *, jsonbtest.idlist_jsonb
from   jsonbtest
where  jsonbtest.idlist_jsonb ?| array['193623800', '895207852'];

Hinweis: ich die Punkt-notation /berechnete Feld hier, aber Sie müssen nicht.

3. Aber an diesem Punkt, Sie nicht haben zu stick mit json[b]: Sie haben eine einfache text-array, das unterstützt wird von PostgreSQL zu.

create function idlist_array(jsonbtest)
  returns text[]
  language sql
  strict
  immutable
as $func$
  select array(select jsonb_array_elements($1.data) ->> 'id')
$func$;

create index on jsonbtest using gin (idlist_array(jsonbtest));

Abfrage und das berechnete Feld mit der überlappung array-operator &&:

select *, jsonbtest.idlist_array
from   jsonbtest
where  jsonbtest.idlist_array && array['193623800', '895207852'];

Hinweis: Von meiner internen Tests, diese letztere Lösung ist berechnet mit einem höheren Preis als die jsonb-Variante, aber in der Tat, es ist schneller als, ein wenig. Wenn die Leistung wirklich wichtig für Sie ist, sollten Sie test sowohl.

InformationsquelleAutor der Antwort pozs