Re: Cast jsonb to numeric, int, float, bool

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