From: | "vibhor(dot)kumar(at)enterprisedb(dot)com" <vibhor(dot)kumar(at)enterprisedb(dot)com> |
---|---|
To: | Larry White <ljw1001(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: inequality testing in jsonb query |
Date: | 2014-07-22 02:51:07 |
Message-ID: | F0D5F5B5-E979-40A5-BC7B-EEB69D5E6CB2@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Jul 21, 2014, at 9:06 PM, Larry White <ljw1001(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Rebecca Clarke | 2014-07-22 09:36:34 | What query currently running within function |
Previous Message | Adrian Klaver | 2014-07-22 02:22:18 | Re: Need r_constraint_name |