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