Re: [HACKERS] Cast jsonb to numeric, int, float, bool

From: Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Cast jsonb to numeric, int, float, bool
Date: 2018-02-28 18:42:45
Message-ID: 9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

01.02.2017 17:41, Anastasia Lubennikova:
> 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:
>
> postgres=# select 'true'::jsonb::text::bool;
>  bool
> ------
>  t
> postgres=# select '1.0'::jsonb::text::numeric;
>  numeric
> ---------
>      1.0
>
>
> This patch implements direct casts from jsonb numeric (jbvNumeric) to
> numeric, int4 and float8,
> and from jsonb bool (jbvBool) to bool.
>
> postgres=# select 'true'::jsonb::bool;
>  bool
> ------
>  t
>
> postgres=# select '1.0'::jsonb::numeric;
>  numeric
> ---------
>      1.0
>
>
> Waiting for your feedback.
> If you find it useful, I can also add support of json and other types,
> such as smallint and bigint.
>
>

I totally forgot about this thread, but it is a small but useful feature.
Maybe it's time to dust it off.

This patch was made by request of one of our clients,
and though they already use custom build, I think it would be better to
have these casts in core.

The patch is attached.
There are no tests yet, since I don't really sure what set of types do
we want to support.
Now the patch provides jsonb to numeric, int4, float8 and bool.

Also I have some doubts about castcontext. But 'explisit' seems to be
the correct one here.

--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
jsonb_numeric,int,float,bool_cast_11.0_v0.patch text/x-patch 6.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Steele 2018-02-28 18:48:49 Re: [PATCH] Verify Checksums during Basebackups
Previous Message Robert Haas 2018-02-28 18:11:16 Re: Server won't start with fallback setting by initdb.