postgresql Postgresql查询JSONB字段中的对象数组

Postgresql query array of objects in JSONB field

提问by user3761100

I have a table in a postgresql 9.4 database with a jsonb field called receivers. Some example rows:

我在 postgresql 9.4 数据库中有一个表，其中有一个名为接收器的 jsonb 字段。一些示例行：

[{"id": "145119603", "name": "145119603", "type": 2}]
[{"id": "1884595530", "name": "1884595530", "type": 1}]
[{"id": "363058213", "name": "363058213", "type": 1}]
[{"id": "1427965764", "name": "1427965764", "type": 1}]
[{"id": "193623800", "name": "193623800", "type": 0}, {"id": "419955814", "name": "419955814", "type": 0}]
[{"id": "624635532", "name": "624635532", "type": 0}, {"id": "1884595530", "name": "1884595530", "type": 1}]
[{"id": "791712670", "name": "791712670", "type": 0}]
[{"id": "895207852", "name": "895207852", "type": 0}]
[{"id": "144695994", "name": "144695994", "type": 0}, {"id": "384217055", "name": "384217055", "type": 0}]
[{"id": "1079725696", "name": "1079725696", "type": 0}]

I have a list of values for id and want to select any row that contains an object with any of the values from that list, within the array in the jsonb field.

我有一个 id 值列表，并希望在 jsonb 字段的数组中选择包含具有该列表中任何值的对象的任何行。

Is that possible? Is there a GIN index I can make that will speed this up?

那可能吗？我可以制作一个 GIN 索引来加快速度吗？

回答by pozs

There is no single operation, which can help you, but you have a few options:

没有单一的操作可以帮助您，但您有几个选择：

1.If you have a small (and fixed) number of ids to query, you can use multiple containment operators @>combined with or; f.ex.:

1.如果您要查询的 id 数量很少（且固定），您可以将多个包含运算符@>与or;结合使用。例如：

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

A simple ginindex can help you on your data column here.

一个简单的gin索引可以帮助您处理这里的数据列。

2.If you have variable number of ids (or you have a lot of them), you can use json[b]_array_elements()to extract each element of the array, build up an id list and then query it with the any-containment operator ?|:

2.如果你有可变数量的id（或者你有很多），你可以使用json[b]_array_elements()提取数组的每个元素，建立一个id列表，然后用any-containment操作符查询它?|：

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

Unfortunately, you cannot index an expression, which has a sub-query in it. If you want to index it, you need to create a function for it:

不幸的是，您无法索引包含子查询的表达式。如果你想索引它，你需要为它创建一个函数：

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

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

After this, you can query ids like this:

在此之后，您可以像这样查询 id：

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

Note: I used dot notation / computed fieldhere, but you don't have to.

注意：我在这里使用了点表示法/计算域，但您不必这样做。

3.But at this point, you don't have to stick with json[b]: you have a simple text array, which is supported by PostgreSQL too.

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

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

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

And query this computed field with the overlap array operator &&:

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

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

Note: From my internal testing, this latter solution is calculated with a higher cost than the jsonb variant, but in fact it is faster than that, a little. If performance really matters to you, you should test both.

注意：根据我的内部测试，后一种解决方案的计算成本高于 jsonb 变体，但实际上它比 jsonb 变体要快一点。如果性能对你来说真的很重要，你应该测试两者。

回答by taiberium

I find workaround:
where data::text similar to '%("id": "145119603"|"id": "1884595530")%'

我找到解决方法：
where data::text similar to '%("id": "145119603"|"id": "1884595530")%'