Re: [HACKERS] Cast jsonb to numeric, int, float, bool - Mailing list pgsql-hackers

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