query nested levels in jsonb

From: wanna_be <thirulok_t(at)hotmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: query nested levels in jsonb
Date: 2015-06-08 14:41:36
Message-ID: 1433774496222-5852918.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Thom Brown 2015-06-08 15:53:43 Re: query nested levels in jsonb
Previous Message Federico Leoni 2015-06-08 12:59:26 [Newbie] Postgresql for Odoo/Python: Concatenate rows value until condition = true?