From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: a.lubennikova@postgrespro.ru
Cc: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Subject: Re: Cast jsonb to numeric, int, float, bool
Date: Wed, 1 Feb 2017 19:26:33 +0300
Message-ID: <55137679-fd6b-baf0-bfbf-6558ce5cb409@postgrespro.ru> (raw)
In-Reply-To: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: n.gluhov@postgrespro.ru, a.lubennikova@postgrespro.ru
Subject: Re: Cast jsonb to numeric, int, float, bool
In-Reply-To: <55137679-fd6b-baf0-bfbf-6558ce5cb409@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox