pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMalformed array literal in goin from jsonb to real[]
3+ messages / 2 participants
[nested] [flat]
* Malformed array literal in goin from jsonb to real[]
@ 2023-03-09 15:19 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Wells Oliver @ 2023-03-09 15:19 UTC (permalink / raw)
To: pgsql-admin
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 some
awful string munging?
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Malformed array literal in goin from jsonb to real[]
@ 2023-03-09 16:05 Erik Wienhold <ewie@ewie.name>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Erik Wienhold @ 2023-03-09 16:05 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Malformed array literal in goin from jsonb to real[]
@ 2023-03-09 16:12 Erik Wienhold <ewie@ewie.name>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 3+ messages in thread
From: Erik Wienhold @ 2023-03-09 16:12 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
> On 09/03/2023 17:05 CET Erik Wienhold <ewie@ewie.name> wrote:
>
> 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)
I just noticed that it's also possible without a custom type:
select * from jsonb_to_record('{"col":[[0,1],[2,3]]}') as t(col real[]);
--
Erik
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-03-09 16:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-09 15:19 Malformed array literal in goin from jsonb to real[] Wells Oliver <wells.oliver@gmail.com>
2023-03-09 16:05 ` Erik Wienhold <ewie@ewie.name>
2023-03-09 16:12 ` Erik Wienhold <ewie@ewie.name>
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