Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37951=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1XjsYX-00005Z-CH
 for pgsql-sql@arkaria.postgresql.org; Thu, 30 Oct 2014 16:26:37 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37951=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1XjsYW-0002zC-TQ
 for pgsql-sql@arkaria.postgresql.org; Thu, 30 Oct 2014 16:26:36 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <tdudgeon.ml@gmail.com>)
 id 1XjsYW-0002z6-3a
 for pgsql-sql@postgresql.org; Thu, 30 Oct 2014 16:26:36 +0000
Received: from mail-wi0-x236.google.com ([2a00:1450:400c:c05::236])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <tdudgeon.ml@gmail.com>)
 id 1XjsYN-0003e4-Pn
 for pgsql-sql@postgresql.org; Thu, 30 Oct 2014 16:26:35 +0000
Received: by mail-wi0-f182.google.com with SMTP id d1so7897306wiv.15
 for <pgsql-sql@postgresql.org>; Thu, 30 Oct 2014 09:26:26 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type;
 bh=KBz7dytXgaBfvW44YJ4WHhFhHBuW99BiUZ8LD1DKG+g=;
 b=0+eS6RLkVy16uNGV5JMb0oc6B67zQxATvZxW4K48Y0xMuibTnhy4yYiN3r9qHUiDFJ
 CNJ26RtVKcv+nNie/aMgmnoV7blWXzvSgqdun9RoItE8zqo76CotpGUccObLGQfeBPzN
 w9DaHMEh8eY9ezJ79WZMh5cPNb6hISaCfu0+BkvsEXRo+mYjOrmClBELl5zdHQcmda1U
 LpQEYX8h0zoXqS/TAuzajTYBKx9fPfm9WLJexS4PX4fNu97QzXznK3l48SnSi/pPokdl
 DZhLNOmws3zJ/vzCkcgv70e7I9nxWG3fCJBWQh1jRIpUkZa/6HUAeRrjkikvF80cc/wb
 llOg==
X-Received: by 10.180.108.144 with SMTP id hk16mr154352wib.68.1414686386568;
 Thu, 30 Oct 2014 09:26:26 -0700 (PDT)
Received: from timbomac.home (host86-154-157-95.range86-154.btcentralplus.com.
 [86.154.157.95])
 by mx.google.com with ESMTPSA id o1sm9200148wja.25.2014.10.30.09.26.24
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 30 Oct 2014 09:26:25 -0700 (PDT)
Message-ID: <54526655.2010306@gmail.com>
Date: Thu, 30 Oct 2014 16:24:53 +0000
From: Tim Dudgeon <tdudgeon.ml@gmail.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
CC: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
Subject: Re: querying within json
References: 
 <CAP=hSSfiqaxt7NFi8qUEjKrH=te_cFXDfOfZ9PYEhcJRC+oGMg@mail.gmail.com>
 <CAFzmHiX5oXGe+kgatVNaq5QqB_BrADtZczHo79x+R4Pecn3vCw@mail.gmail.com>
In-Reply-To: 
 <CAFzmHiX5oXGe+kgatVNaq5QqB_BrADtZczHo79x+R4Pecn3vCw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------040909090801040401050200"
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------040909090801040401050200
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 30/10/2014 15:22, Giuseppe Broccolo wrote:
> Hi Tim,
>
> 2014-10-30 15:55 GMT+01:00 Tim Dudgeon <tdudgeon.ml@gmail.com 
> <mailto:tdudgeon.ml@gmail.com>>:
>
>     Any advice on how to best query for values within json (using
>     9.4). I have numeric fields within the json and want to include
>     terms for those fields.
>
>     I've found that something like this works:
>
>     select * from atable where (json_col->>'numeric_prop')::numeric < 100;
>
>     But whilst that works:
>     1. seems to have unnecessary casts? The numeric _prop item is of
>     numeric type, but its getting retrieved as text and then cast to
>     numeric and then compared
>
>
> The right operand type of the ->> oeprator is text when ->> is used to 
> get a json object field. So the cast to numeric is needed.

Needed to work, yes. But if my reading of the docs is right then numeric 
types within json as supposed to be treated as Postgres numeric type?
See table 8.23 here: 
http://www.postgresql.org/docs/9.4/static/datatype-json.html
So this would mean a cast from numeric to text and then back to numeric?
There is no way to ask for a json 'field' in its actual data type so 
avoiding the cast?

>     2. its not going to use any index on the json_col jsonb column.
>
>
> The usage of an index is mostly ruled by the 'selectivity' of the 
> query. Anyway, if querying for particular items within the key is 
> common (as 'numeric_prop' in your example), defining an index like 
> this may be worthwhile:
>
> CREATE INDEX idxgin_numeric_prop ON atable USING 
> gin((json_col->'numeric_prop'));

I can add the index, but no evidence of it being used when I run a query 
like this:
select * from atable where (json_col->>'numeric_prop')::numeric < 100;

Tim

>
> Regards,
> -- 
> Giuseppe Broccolo - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> giuseppe.broccolo@2ndQuadrant.it 
> <mailto:giuseppe.broccolo@2ndQuadrant.it> | www.2ndQuadrant.it 
> <http://www.2ndQuadrant.it>


--------------040909090801040401050200
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    On 30/10/2014 15:22, Giuseppe Broccolo wrote:<br>
    <blockquote
cite="mid:CAFzmHiX5oXGe+kgatVNaq5QqB_BrADtZczHo79x+R4Pecn3vCw@mail.gmail.com"
      type="cite">
      <div dir="ltr">Hi Tim,<br>
        <div>
          <div class="gmail_extra"><br>
            <div class="gmail_quote">2014-10-30 15:55 GMT+01:00 Tim
              Dudgeon <span dir="ltr">&lt;<a moz-do-not-send="true"
                  href="mailto:tdudgeon.ml@gmail.com" target="_blank">tdudgeon.ml@gmail.com</a>&gt;</span>:<br>
              <blockquote class="gmail_quote" style="margin:0px 0px 0px
                0.8ex;border-left:1px solid
                rgb(204,204,204);padding-left:1ex">
                <div dir="ltr">Any advice on how to best query for
                  values within json (using 9.4). I have numeric fields
                  within the json and want to include terms for those
                  fields.<br>
                  <br>
                  I've found that something like this works:<br>
                  <br>
                  select * from atable where
                  (json_col-&gt;&gt;'numeric_prop')::numeric &lt; 100;<br>
                  <br>
                  But whilst that works:<br>
                  1. seems to have unnecessary casts? The numeric _prop
                  item is of numeric type, but its getting retrieved as
                  text and then cast to numeric and then compared<br>
                </div>
              </blockquote>
              <div><br>
              </div>
              <div>The right operand type of the -&gt;&gt; oeprator is
                text when -&gt;&gt; is used to get a json object field.
                So the cast to numeric is needed.<tt class=""><br>
                </tt></div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    Needed to work, yes. But if my reading of the docs is right then
    numeric types within json as supposed to be treated as Postgres
    numeric type? <br>
    See table 8.23 here:
    <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/9.4/static/datatype-json.html">http://www.postgresql.org/docs/9.4/static/datatype-json.html</a><br>
    So this would mean a cast from numeric to text and then back to
    numeric?<br>
    There is no way to ask for a json 'field' in its actual data type so
    avoiding the cast?<br>
    <br>
    <blockquote
cite="mid:CAFzmHiX5oXGe+kgatVNaq5QqB_BrADtZczHo79x+R4Pecn3vCw@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div>
          <div class="gmail_extra">
            <div class="gmail_quote">
              <div> </div>
              <blockquote class="gmail_quote" style="margin:0px 0px 0px
                0.8ex;border-left:1px solid
                rgb(204,204,204);padding-left:1ex">
                <div dir="ltr">
                  2. its not going to use any index on the json_col
                  jsonb column.<br>
                </div>
              </blockquote>
              <div><br>
              </div>
              <div>The usage of an index is mostly ruled by the
                'selectivity' of the query. Anyway, if querying for
                particular items within the key is common (as
                'numeric_prop' in your example), defining an index like
                this may be worthwhile:<br>
                <br>
              </div>
              <div>CREATE INDEX idxgin_numeric_prop ON atable USING
                gin((json_col-&gt;'numeric_prop'));<br>
              </div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    I can add the index, but no evidence of it being used when I run a
    query like this:<br>
    select * from atable where
    (json_col-&gt;&gt;'numeric_prop')::numeric &lt; 100;<br>
    <br>
    Tim<br>
    <br>
    <blockquote
cite="mid:CAFzmHiX5oXGe+kgatVNaq5QqB_BrADtZczHo79x+R4Pecn3vCw@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div>
          <div class="gmail_extra">
            <div class="gmail_quote">
              <div><br>
              </div>
              <div>Regards,</div>
            </div>
            -- <br>
            <div class="gmail_signature">
              <div dir="ltr"><span></span>Giuseppe Broccolo -
                2ndQuadrant Italy
                <br>
                PostgreSQL Training, Services and Support
                <br>
                <a moz-do-not-send="true"
                  href="mailto:giuseppe.broccolo@2ndQuadrant.it"
                  target="_blank">giuseppe.broccolo@2ndQuadrant.it</a> |
                <a moz-do-not-send="true"
                  href="http://www.2ndQuadrant.it" target="_blank">www.2ndQuadrant.it</a>
              </div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------040909090801040401050200--