In-Reply-To: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
To: a.lubennikova@postgrespro.ru
Cc: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Subject: Re: Cast jsonb to numeric, int, float, bool
Organization: Postgres Professional
Message-ID: <55137679-fd6b-baf0-bfbf-6558ce5cb409@postgrespro.ru>
Date: Wed, 1 Feb 2017 19:26:33 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.7.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 01.02.2017 14:21,Anastasia Lubennikova wrote:
> Now the simplest way to extract booleans and numbers from json/jsonb is
> to cast it to text and then cast to the appropriate type: ...
> This patch implements direct casts from jsonb numeric (jbvNumeric) to
> numeric, int4 and float8, and from jsonb bool (jbvBool) to bool.

Thank you for this patch. I always wanted to add such casts by myself.


> If you find it useful, I can also add support of json and other types,
> such as smallint and bigint.

Yes, I'd like to have support for other types and maybe for json.


Some comments about the code: I think it would be better to
  * add function for extraction of scalars from pseudo-arrays
  * iterate until WJB_DONE to pfree iterator

Example:

static bool
JsonbGetScalar(Jsonb *jb, JsonbValue *v)
{
     JsonbIterator *it;
     JsonbIteratorToken tok;
     JsonbValue jbv;

     if (!JB_ROOT_IS_SCALAR(jb))
         return false;

     /*
      * A root scalar is stored as an array of one element, so we get the
      * array and then its first (and only) member.
      */
     it = JsonbIteratorInit(&jb->root);

     tok = JsonbIteratorNext(&it, &jbv, true);
     Assert(tok == WJB_BEGIN_ARRAY);

     tok = JsonbIteratorNext(&it, v, true);
     Assert(tok == WJB_ELEM);

     tok = JsonbIteratorNext(&it, &jbv, true);
     Assert(tok == WJB_END_ARRAY);
	
     tok = JsonbIteratorNext(&it, &jbv, true);
     Assert(tok == WJB_DONE);

     return true;
}

Datum
jsonb_int4(PG_FUNCTION_ARGS)
{
     Jsonb      *in = PG_GETARG_JSONB(0);
     JsonbValue  v;

     if (!JsonbGetScalar(in, &v) || v.type != jbvNumeric)
         ereport(ERROR,
                 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                  errmsg("key value must be json numeric")));

     PG_RETURN_INT32(DatumGetInt32(DirectFunctionCall1(numeric_int4,
                                                       NumericGetDatum(v.val.numeric))));
}

-- 
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers