Re: query nested levels in jsonb

On 8 June 2015 at 15:41, wanna_be <thirulok_t(at)hotmail(dot)com> wrote:
> can you please help me getting query for the following , i couldn't figure
> out this
> select all from orders table where details->quantity is greater than 2?
>
> select * from orders Where document->'orderid'='3' -- this works , I get
> results back
>
> select * from orders Where document->'key'<'90' -- -- this works , I get
> results back
>
> Hers's the schema
>
> create table orders (document jsonb)
>
> insert into orders values
> ('{"orderid":3,"key":100,"total":3510.20,"ref_id":"AFV",
> "details":
> [
> {"product":3,"quantity":20,"price":2.1,"c":"something"},
> {"product":13,"quantity":2,"price":1.1},
> {"product":18,"quantity":4,"price":0.3}
> ]
> }')
> Insert Into orders values ('{"orderid":2, "key":20, "total":510.20,
> "ref_id":"zzz"}')
> insert into orders values ('{"orderid":5, "key":100, "total":3510.20,
> "ref_id":"AFV",
> "details":
> [
> {"product":3,"quantity":20,"price":2.1},
> {"product":13,"quantity":2,"price":1.1},
> {"product":18,"quantity":4,"price":0.3}
> ]
> }')
> insert into orders values ('{"ref_id": "AFV", "total": 3510.20,
> "details":
> [
> {"c": "something", "price": 2.1, "product": 3, "quantity": 20},
> {"price": 1.1, "product": 13, "quantity": 2},
> {"price": 0.3, "product": 18, "quantity": 4}
> ], "orderid": 3, "key": 100}')

As the details value is an array of objects, there's no non-hacky way
of doing it.

To do this, you'd need something like JsQuery
(https://github.com/postgrespro/jsquery) which would allow you to get
the result you're looking for:

SELECT * FROM orders WHERE document @@ 'details.#.quantity > 2'::jsquery;

This selects rows where the "details" value contains an element which
contains a "quantity" that has a value greater than 2.

Note, however, that JsQuery is still considered to be under development.

--
Thom