Обсуждение: inequality testing in jsonb query
Hi,
Is it possible to query a table with a jsob column to find values that were in some range? For example, If I have a document like this (from the PG documentation:
{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
"name": "Angela Barton",
"is_active": true,
"company": "Magnafone",
"address": "178 Howard Place, Gulf, Washington, 702",
"registered": "2009-11-07T08:53:22 +08:00",
"latitude": 19.793713,
"longitude": 86.513373,
"tags": [
"enim",
"aliquip",
"qui"
]
}
Could I modify the following query to find those records where the date "registered" is between November 1, 2009 and November 30, 2009?
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';
Thank you for your help.
larry
On Jul 21, 2014, at 9:06 PM, Larry White <ljw1001@gmail.com> wrote:
Is it possible to query a table with a jsob column to find values that were in some range? For example, If I have a document like this (from the PG documentation:{"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a","name": "Angela Barton","is_active": true,"company": "Magnafone","address": "178 Howard Place, Gulf, Washington, 702","registered": "2009-11-07T08:53:22 +08:00","latitude": 19.793713,"longitude": 86.513373,"tags": ["enim","aliquip","qui"]}Could I modify the following query to find those records where the date "registered" is between November 1, 2009 and November 30, 2009?SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}’;
Yes, you can try something like given below:
SELECT jdoc->'guid', jdoc->'name',(jdoc->'registered')::text::timestamptz
FROM api
WHERE (jdoc->'registered')::text::timestamptz BETWEEN '2009-11-01'::date AND '2009-11-30'::date;
Thanks & Regards,
Vibhor Kumar
(EDB) EnterpriseDB Corporation
The Postgres Database Company
Blog:http://vibhork.blogspot.com
Vibhor Kumar
(EDB) EnterpriseDB Corporation
The Postgres Database Company
Blog:http://vibhork.blogspot.com