Postgresql查询JSONB字段中的对象数组
Postgresql查询JSONB字段中的对象数组
提问于 2015-02-13 03:31:49
我在PostgreSQL9.4数据库中有一个表，其中包含一个名为receivers的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}]我有一个id值列表，并希望在jsonb字段的数组中选择包含该列表中任意值的对象的任何行。
这有可能吗？有没有我可以创建的杜松子酒索引，可以加快速度？
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-02-13 18:22:15
没有可以帮助您的单一操作，但您有以下几个选择：
1.如果要查询的If数量较少(且固定)，则可以结合使用多个包含运算符@>和or；f.ex。：
where data @> '[{"id": "1884595530"}]' or data @> '[{"id": "791712670"}]'在这里，一个简单的gin索引可以帮助您处理数据列。
2.如果您有数量可变的id (或者有很多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));之后，您可以像这样查询ids：
select *, jsonbtest.idlist_jsonb
from jsonbtest
where jsonbtest.idlist_jsonb ?| array['193623800', '895207852'];注意:我在这里使用了dot notation / computed field，但您不必这样做。
Jsonb3.jsonb但在这一点上，您不必坚持使用：您有一个简单的文本数组，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'];注意:根据我的内部测试，后一种解决方案的计算成本高于jsonb变体，但实际上它比jsonb快一点。如果性能对您来说真的很重要，那么您应该同时测试这两种性能。
Stack Overflow用户
发布于 2017-06-22 23:41:15
我找到了解决方法：
where data::text similar to '%("id": "145119603"|"id": "1884595530")%'
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28486192
复制相关文章
相似问题
腾讯云开发者
