CODEWITHSUNDEEP
postgresqlpsql
Ben Smith
Ben Smith

Reputation: 861

Postgres JSONB: query values in JSON array

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?

Upvotes: 1

Views: 1511

Answers (1)

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

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;

Upvotes: 7

Related Questions