JSONB字段中的对象的Postgresql查询数组 [英] Postgresql query array of objects in JSONB field

问题描述

我在PostgreSQL 9.4数据库中有一个表，表中有一个称为接收器的jsonb字段.一些示例行:

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

[{"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}]

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

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.

有可能吗?我可以建立一个GIN索引来加快速度吗?

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

推荐答案

没有可以帮助您的单个操作，但是您有一些选择:

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

1..如果要查询的ID数量很少(固定)，则可以将多个包含运算符@>与or结合使用；例:

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

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

简单的gin索引可以在此处帮助您访问数据列.

A simple gin index can help you on your data column here.

2..如果您拥有可变数量的ID(或者有很多ID)，则可以使用json[b]_array_elements()提取数组的每个元素，建立一个ID列表，然后使用任何包含内容的运算符?|进行查询:

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 ?|:

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($1.data) ->> 'id'))::jsonb
$func$;

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

此后，您可以查询以下ID:

After this, you can query ids like this:

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

注意:我使用了点符号/计算字段在这里，但您不必这样做.

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

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

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.

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

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

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

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

注意:根据我的内部测试，后一种解决方案的计算成本要比jsonb变体高，但实际上它要快得多.如果性能确实对您很重要，则应同时进行测试.

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字段中的对象的Postgresql查询数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！