Re: inequality testing in jsonb query

Поиск
Список
Период
Сортировка
От vibhor.kumar@enterprisedb.com
Тема Re: inequality testing in jsonb query
Дата
Msg-id F0D5F5B5-E979-40A5-BC7B-EEB69D5E6CB2@enterprisedb.com
обсуждение исходный текст
Ответ на inequality testing in jsonb query  (Larry White <ljw1001@gmail.com>)
Список pgsql-general
Дерево обсуждения

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

В списке pgsql-general по дате отправления: