Re: query nested levels in jsonb - Mailing list pgsql-novice

On 8 June 2015 at 15:41, wanna_be <thirulok_t@hotmail.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