From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | wanna_be <thirulok_t(at)hotmail(dot)com> |
Cc: | PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: query nested levels in jsonb |
Date: | 2015-06-08 15:53:43 |
Message-ID: | CAA-aLv7-TYwPHTL49KoGjcbev_aUF67aV6jb70tnjG0sEp90fg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | wanna_be | 2015-06-08 16:05:37 | Re: query nested levels in jsonb |
Previous Message | wanna_be | 2015-06-08 14:41:36 | query nested levels in jsonb |