Postgres Pro
Downloads
Home   >   mailing lists

Thread: Casting json (or jsonb) to real

Casting json (or jsonb) to real

From
Wells Oliver
Date:
I have a jsonb column which just stores a value, not an object with key/value, e..g

"right"
"[0.563426, -0.104352, 0.819550]"
"[-1.024841, -0.235817, 0.591118]"
"1.4531"

When I know this value is a floating point value I would just like to cast

SELECT value::real but you can't do that. You can do SELECT (jsonbcol->>'somekey')::real, but these columns just store a value.

What are my options here? This is json data, sometimes it is an array, etc, but for certain rows I know it to be a floating point value.

--
Wells Oliver
wells.oliver@gmail.com

Re: Casting json (or jsonb) to real

From
Tom Lane
Date:
Wells Oliver <wells.oliver@gmail.com> writes:
> When I know this value is a floating point value I would just like to cast
> SELECT value::real but you can't do that. You can do SELECT
> (jsonbcol->>'somekey')::real, but these columns just store a value.

Works for me, in v11 and up:

regression=# select '1.4531'::jsonb::real;
 float4 
--------
 1.4531
(1 row)

In older versions you could do

regression=# select '1.4531'::jsonb::text::real;
 float4 
--------
 1.4531
(1 row)

I fear that either one involves a conversion to text and back
under the hood :-(.  Now, if you were casting to numeric,
that would be very cheap.

            regards, tom lane

Re: Casting json (or jsonb) to real

From
Wells Oliver
Date:
Well, it's because the representation of the value is getting double quoted, so it's 

select '"1.453"'::real, which yields ERROR:  cannot cast jsonb string to type real.

I have all this serialized string data stored in the jsonb col, all of which is double quoted.

On Tue, May 31, 2022 at 5:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> When I know this value is a floating point value I would just like to cast
> SELECT value::real but you can't do that. You can do SELECT
> (jsonbcol->>'somekey')::real, but these columns just store a value.

Works for me, in v11 and up:

regression=# select '1.4531'::jsonb::real;
 float4
--------
 1.4531
(1 row)

In older versions you could do

regression=# select '1.4531'::jsonb::text::real;
 float4
--------
 1.4531
(1 row)

I fear that either one involves a conversion to text and back
under the hood :-(.  Now, if you were casting to numeric,
that would be very cheap.

                        regards, tom lane


--

Re: Casting json (or jsonb) to real

From
Wells Oliver
Date:
Sorry, of course, the correct SQL throwing the error is:

select '"1.453"'::jsonb::real;

On Tue, May 31, 2022 at 6:08 PM Wells Oliver <wells.oliver@gmail.com> wrote:
Well, it's because the representation of the value is getting double quoted, so it's 

select '"1.453"'::real, which yields ERROR:  cannot cast jsonb string to type real.

I have all this serialized string data stored in the jsonb col, all of which is double quoted.

On Tue, May 31, 2022 at 5:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> When I know this value is a floating point value I would just like to cast
> SELECT value::real but you can't do that. You can do SELECT
> (jsonbcol->>'somekey')::real, but these columns just store a value.

Works for me, in v11 and up:

regression=# select '1.4531'::jsonb::real;
 float4
--------
 1.4531
(1 row)

In older versions you could do

regression=# select '1.4531'::jsonb::text::real;
 float4
--------
 1.4531
(1 row)

I fear that either one involves a conversion to text and back
under the hood :-(.  Now, if you were casting to numeric,
that would be very cheap.

                        regards, tom lane


--


--

Re: Casting json (or jsonb) to real

From
Tom Lane
Date:
Wells Oliver <wells.oliver@gmail.com> writes:
> Well, it's because the representation of the value is getting double
> quoted, so it's
> select '"1.453"'::real, which yields ERROR:  cannot cast jsonb string to
> type real.

Oh ... that perhaps wasn't a great choice.  But it looks like you could
pretend the jsonb is a 1-element array, and do

regression=# select ('"1.4531"'::jsonb->>0)::real;
 float4 
--------
 1.4531
(1 row)

            regards, tom lane

Re: Casting json (or jsonb) to real

From
Wells Oliver
Date:
Thanks, yeah, a lot of this stored by json.dumps(v) in python, so this will allow me to update the column...

On Tue, May 31, 2022 at 6:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> Well, it's because the representation of the value is getting double
> quoted, so it's
> select '"1.453"'::real, which yields ERROR:  cannot cast jsonb string to
> type real.

Oh ... that perhaps wasn't a great choice.  But it looks like you could
pretend the jsonb is a 1-element array, and do

regression=# select ('"1.4531"'::jsonb->>0)::real;
 float4
--------
 1.4531
(1 row)

                        regards, tom lane


--