From: Erik Wienhold <ewie@ewie.name>
To: Wells Oliver <wells.oliver@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Malformed array literal in goin from jsonb to real[]
Date: Thu, 9 Mar 2023 17:05:56 +0100 (CET)
Message-ID: <84644851.192870.1678377956057@office.mailbox.org> (raw)
In-Reply-To: <CAOC+FBWKkoHWmCu+RtkTcJ76FkeqaNTSuPVtzvp=SBK6xbv+kQ@mail.gmail.com>
References: <CAOC+FBWKkoHWmCu+RtkTcJ76FkeqaNTSuPVtzvp=SBK6xbv+kQ@mail.gmail.com>
> On 09/03/2023 16:19 CET Wells Oliver <wells.oliver@gmail.com> wrote:
>
> This value exists in my jsonb column:
>
> [[-0.7452975, -0.21457797, 0.631259], [0.66549873, -0.2969812, 0.6847727], [0.04053491, 0.9304614, 0.3641407]]
>
> Which obviously blows up when doing (jsonb->>col)::real[] with malformed
> array literal.
>
> Is there a convenient function to do this casting or do I need to do somes
> awful string munging?
Use jsonb_populate_record with a custom type:
create type myrec as (col real[][]);
select * from jsonb_populate_record(null::myrec, '{"col":[[0,1],[2,3]]}');
col
---------------
{{0,1},{2,3}}
(1 row)
--
Erik
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-admin@postgresql.org
Cc: ewie@ewie.name, wells.oliver@gmail.com
Subject: Re: Malformed array literal in goin from jsonb to real[]
In-Reply-To: <84644851.192870.1678377956057@office.mailbox.org>
* 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