Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite
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?

share|improve this question
up vote 2 down vote accepted

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;
share|improve this answer
    
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 3 '15 at 8:16
    
@BenSmith I have updated the answer – Dmitry Savinkov Sep 3 '15 at 16:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.