PostgreSQL 查询 JSONB 字段中的对象数组

没有单一的操作可以帮助您，但您有几个 Select :

1.如果要查询的ID数量较少(且固定)，可以使用多个包含运算符@>和or；f、 例:

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

一个简单的gin索引可以帮助你在这里的数据列.

2.若你们有可变数量的id(或者你们有很多)，你们可以用json[b]_array_elements()来提取数组的每个元素，建立一个id列表，然后用任何包含操作符?|来查询它:

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

不幸的是，您无法为表达式编制索引，因为其中包含子查询.如果要为其编制索引，需要为其创建一个函数:

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));

之后，您可以像这样查询ID:

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

Note:我这里用了dot notation / computed field，但你不必.

3.但在这一点上，你不必坚持使用json[b]:你有一个简单的文本数组，PostgreSQL也支持它.

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));

并使用重叠数组运算符&&查询该计算字段:

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

Note:从我的内部测试来看，后一种解决方案的计算成本高于jsonb变体，但实际上它比jsonb变体快一点.如果性能对你来说真的很重要，你应该同时测试这两种性能.