inequality testing in jsonb query

From: Larry White <ljw1001(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: inequality testing in jsonb query
Date: 2014-07-22 02:06:37
Message-ID: CAMdbzVgiwdq-7xijz7s8eUyvzm1WeV0nAZcdmFQz_bHW6n=W_A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-07-22 02:22:18 Re: Need r_constraint_name
Previous Message Adrian Klaver 2014-07-22 00:34:57 Re: Question regarding log files in postgresql