Message-ID: <b9ffb897998ec715ff5ced0204e9f89631d260da.camel@cybertec.at>
Subject: Re: Convert JSON value back to postgres representation
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Phillip Diffley <phillip6402@gmail.com>, pgsql-general@postgresql.org
Date: Fri, 20 Jun 2025 06:59:41 +0200
In-Reply-To: <CAGAwPgSCTk=HWKtrzf4L+DOdCFDNOZY0Np9go0nsT6wqe6jd=w@mail.gmail.com>
References: 
	<CAGAwPgSCTk=HWKtrzf4L+DOdCFDNOZY0Np9go0nsT6wqe6jd=w@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/b9ffb897998ec715ff5ced0204e9f89631d260da.camel%40cybertec.at>
Precedence: bulk

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 h=
oping there would
> be something like a from_jsonb function that, along with a type hint, cou=
ld be used as an
> inverse of to_jsonb, like=C2=A0
>=20
> from_jsonb(to_jsonb('{1,2,3}'::int[]) as int[]
>=20
> 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 th=
e WHERE expression
> below, but I feel like there might be a better way to do this.=C2=A0
>=20
> CREATE TABLE mytable (id int, col1 int[]);
> INSERT INTO mytable VALUES (1, '{1, 2, 3}'), (2, '{3, 4, 5}');
> SELECT * from mytable WHERE col1 =3D (select col1 from json_to_record('{"=
col1": [1, 2, 3]}'::JSON) as x(col1 int[]));
>=20
> 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.
Not quite right, but:

  CREATE TEMP TABLE arr(a integer[]);

  SELECT * FROM jsonb_populate_record(
                   NULL::arr,
                   jsonb_build_object('a', to_jsonb(ARRAY[1, 2, 3]))
                );

      a   =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90
   {1,2,3}
  (1 row)

Yours,
Laurenz Albe