Обсуждение: query nested levels in jsonb

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}')





--
View this message in context: http://postgresql.nabble.com/query-nested-levels-in-jsonb-tp5852918.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

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

Thanks I will try JsQuery out.

Also is there any other way to manipulate the resultset, something like
selecting all the rows that have "details" into a temporary structure and
querying that?

SELECT * FROM orders WHERE document @> '[]' AND document ->> 'details' >
'1';



--
View this message in context: http://postgresql.nabble.com/query-nested-levels-in-jsonb-tp5852918p5852937.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.