Re: Convert JSON value back to postgres representation

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: Phillip Diffley <phillip6402(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Convert JSON value back to postgres representation
Date: 2025-06-20 05:17:55
Message-ID: CAKFQuwZJCdJZAAJJnmU8cwiD=-=dLcnaRfJ-LxAL8zdH5Ft3QA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thursday, June 19, 2025, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Thu, 2025-06-19 at 23:05 +0200, Phillip Diffley wrote:
> > Postgres has a to_jsonb function that will convert a value into its
> jsonb representation.
> > I am now trying to turn a json value back into its postgres type. I was
> hoping there would
> > be something like a from_jsonb function that, along with a type hint,
> could be used as an
> > inverse of to_jsonb, like
> >
> > from_jsonb(to_jsonb('{1,2,3}'::int[]) as int[]
> >
> > but I do not see a function like this. I was able to convert a json
> value back to its
> > postgres representation using the jsonb_to_record function, as used in
> the WHERE expression
> > below, but I feel like there might be a better way to do this.
> >
> > CREATE TABLE mytable (id int, col1 int[]);
> > INSERT INTO mytable VALUES (1, '{1, 2, 3}'), (2, '{3, 4, 5}');
> > SELECT * from mytable WHERE col1 = (select col1 from
> json_to_record('{"col1": [1, 2, 3]}'::JSON) as x(col1 int[]));
> >
> > Is there a preferred method for turning a JSON value back to its
> postgres representation?
>
> I think jsonb_populate_record() is the closest thing to what you envision.
>

jsonb_to_record avoids the temporary type.

select * from jsonb_to_record('{"ia":[1,2,3]}'::jsonb) as r (ia integer[]);

There is a gap for arrays. Scalars you can just cast and composites have
these functions. But no simple/direct way to go from json array to sql
array is presently implemented.

Though since 17 json_query can apparently do it.

select pg_typeof( json_query('[1,2,3]'::jsonb, '$' returning integer[]) )
-> integer[]

David J.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message legrand legrand 2025-06-20 05:28:07 Re : does multi host connection string supports login event trigger exception
Previous Message Laurenz Albe 2025-06-20 04:59:41 Re: Convert JSON value back to postgres representation