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
|
