Casting json (or jsonb) to real

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Casting json (or jsonb) to real
Date: 2022-06-01 00:31:26
Message-ID: CAOC+FBW85OU9bRFC0ozNtiWFcGkMjCLv3fuVH8LSbiZ8qcX8_g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(dot)oliver(at)gmail(dot)com

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2022-06-01 00:50:51 Re: Casting json (or jsonb) to real
Previous Message Nikhil Shetty 2022-05-31 15:28:43 Fwd: Adding more detail to pg_upgrade documentation