Postgres JSONB：JSON数组中的查询值 [英] Postgres JSONB: query values in JSON array

问题描述

Postgres 9.4

Postgres 9.4

我有一条记录具有这样的JSONB值：

I have a record with a JSONB value like this:

{
  "attributeA": 1,
  "attributeB": "Foo", 
  "arrayAttribute": [
   {"attributeC": 95, "attributeD": 5}, 
   {"attributeC": 105, "attributeD": 5}
  ]
}

我想写一个查询，显示：

I want to write a query which says:

查找attributeA = 1，attributeB ='Foo'和对于arrayAttribute数组中的每个元素，attributeC在某个值X的10点范围内。因此，如果X为100，则上面的记录将匹配（95和105都在100的10点之内）。

Find any item where attributeA = 1, attributeB = 'Foo', and for each element in the arrayAttribute array, attributeC is within a 10 point range of some value X. So if X was 100, the above record would match (both 95 and 105 are within 10 points from 100).

不幸的是，我真的在JSONB查询语法方面苦苦挣扎。最好的方法是什么？

I'm really struggling with the JSONB query syntax unfortunately. What's the best way to do this?

推荐答案

Postgres 关于json的文档确实很棒。对于搜索查询方法，重要的是要知道->> 返回 text 和 -> 返回 json（b）。

Postgres documentation regarding json is really great. As for search query approach it's important to know that ->> returns text and -> returns json(b).

查询可以是以下内容：

Query can be the following:

select * from json js,jsonb_array_elements(data->'arrayAttribute') as array_element  
where (js.data->>'attributeA')::integer = 1 
and js.data->>'attributeB' = 'Foo' 
and (array_element->>'attributeC')::integer >= (100-5) 
and (array_element->>'attributeC')::integer <= (100+5);

如果要按索引选择特定的数组元素，则查询如下：

If you want to select particular array element by index, in your case query will be the following:

SELECT * FROM json js,jsonb_extract_path(data,'arrayAttribute') AS entireArray 
WHERE (entireArray -> 0 ->> 'attributeC')::integer = 95
AND (entireArray -> 1 ->> 'attributeC')::integer = 105;

这篇关于Postgres JSONB：JSON数组中的查询值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！