MIME-Version: 1.0
From: Phillip Diffley <phillip6402@gmail.com>
Date: Thu, 19 Jun 2025 23:05:35 +0200
Message-ID: <CAGAwPgSCTk=HWKtrzf4L+DOdCFDNOZY0Np9go0nsT6wqe6jd=w@mail.gmail.com>
Subject: Convert JSON value back to postgres representation
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000324cb0637f31c0a"
Archived-At: <https://www.postgresql.org/message-id/CAGAwPgSCTk%3DHWKtrzf4L%2BDOdCFDNOZY0Np9go0nsT6wqe6jd%3Dw%40mail.gmail.com>
Precedence: bulk

--0000000000000324cb0637f31c0a
Content-Type: text/plain; charset="UTF-8"

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?

Thank you,
Phillip

--0000000000000324cb0637f31c0a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div>Postgres has a to_jsonb function tha=
t will convert a value into its jsonb representation. I am now trying to tu=
rn a json value back into its postgres type. I was hoping there would be so=
mething like a from_jsonb function that, along with a type hint, could be u=
sed as an inverse of to_jsonb, like=C2=A0</div><div><br></div><div>from_jso=
nb(to_jsonb(&#39;{1,2,3}&#39;::int[]) as int[]</div><div><br></div><div>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.=C2=A0</div><div><br></div><div>CREATE TABLE mytable (id int, c=
ol1 int[]);</div><div>INSERT INTO mytable VALUES (1, &#39;{1, 2, 3}&#39;), =
(2, &#39;{3, 4, 5}&#39;);</div><div>SELECT * from mytable WHERE col1 =3D (s=
elect col1 from json_to_record(&#39;{&quot;col1&quot;: [1, 2, 3]}&#39;::JSO=
N) as x(col1 int[]));</div><div><br></div>Is there a preferred method for t=
urning a JSON value back to its postgres representation?</div><div dir=3D"l=
tr"><br></div><div>Thank you,</div><div>Phillip</div></div>

--0000000000000324cb0637f31c0a--