1

Postgres 9.4

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:

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

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

Dmitry S
  • 4,990
  • 2
  • 24
  • 32
Ben Smith
  • 851
  • 2
  • 10
  • 22

1 Answers1

7

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;
Dmitry S
  • 4,990
  • 2
  • 24
  • 32
  • Thanks, this was helpful. However is there also a way to query on a specific index of the array? e.g. if arrayAttribute[0].attributeC = 50 and arrayAttribute[1].attributeC = 100? – Ben Smith Sep 03 '15 at 08:16