MIME-Version: 1.0
References: <CAGAwPgSCTk=HWKtrzf4L+DOdCFDNOZY0Np9go0nsT6wqe6jd=w@mail.gmail.com>
 <b9ffb897998ec715ff5ced0204e9f89631d260da.camel@cybertec.at> <CAKFQuwZJCdJZAAJJnmU8cwiD=-=dLcnaRfJ-LxAL8zdH5Ft3QA@mail.gmail.com>
In-Reply-To: <CAKFQuwZJCdJZAAJJnmU8cwiD=-=dLcnaRfJ-LxAL8zdH5Ft3QA@mail.gmail.com>
From: Phillip Diffley <phillip6402@gmail.com>
Date: Fri, 20 Jun 2025 19:19:20 +0200
Message-ID: <CAGAwPgRqSZbbsS1bS891dgyEsYZGt1eN=aSiHFYxRYM0MY6hvA@mail.gmail.com>
Subject: Re: Convert JSON value back to postgres representation
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, 
	"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c249a6063804108d"
Archived-At: <https://www.postgresql.org/message-id/CAGAwPgRqSZbbsS1bS891dgyEsYZGt1eN%3DaSiHFYxRYM0MY6hvA%40mail.gmail.com>
Precedence: bulk

--000000000000c249a6063804108d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Good to know. Thank you!

On Fri, Jun 20, 2025 at 7:17=E2=80=AFAM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Thursday, June 19, 2025, Laurenz Albe <laurenz.albe@cybertec.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 wa=
s
>> 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 =3D (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 envisio=
n.
>>
>
> 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.
>
>

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

<div dir=3D"ltr">Good to know. Thank you!</div><br><div class=3D"gmail_quot=
e"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jun 20, 2025 at 7:17=E2=80=
=AFAM David G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com" t=
arget=3D"_blank">david.g.johnston@gmail.com</a>&gt; wrote:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex">On Thursday, June 19, 2025, Laur=
enz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at" target=3D"_blank">=
laurenz.albe@cybertec.at</a>&gt; wrote:<br><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">On Thu, 2025-06-19 at 23:05 +0200, Phillip Diffley wrote:=
<br>
&gt; Postgres has a to_jsonb function that will convert a value into its js=
onb representation.<br>
&gt; I am now trying to turn a json value back into its postgres type. I wa=
s hoping there would<br>
&gt; be something like a from_jsonb function that, along with a type hint, =
could be used as an<br>
&gt; inverse of to_jsonb, like=C2=A0<br>
&gt; <br>
&gt; from_jsonb(to_jsonb(&#39;{1,2,3}&#39;::int[]) as int[]<br>
&gt; <br>
&gt; but I do not see a function like this. I was able to convert a json va=
lue back to its<br>
&gt; postgres representation using the jsonb_to_record function, as used in=
 the WHERE expression<br>
&gt; below, but I feel like there might be a better way to do this.=C2=A0<b=
r>
&gt; <br>
&gt; CREATE TABLE mytable (id int, col1 int[]);<br>
&gt; INSERT INTO mytable VALUES (1, &#39;{1, 2, 3}&#39;), (2, &#39;{3, 4, 5=
}&#39;);<br>
&gt; SELECT * from mytable WHERE col1 =3D (select col1 from json_to_record(=
&#39;{&quot;col1&quot;: [1, 2, 3]}&#39;::JSON) as x(col1 int[]));<br>
&gt; <br>
&gt; Is there a preferred method for turning a JSON value back to its postg=
res representation?<br>
<br>
I think jsonb_populate_record() is the closest thing to what you envision.<=
br>
</blockquote><div><br></div><div>jsonb_to_record avoids the temporary type.=
</div><div><br></div><div>select * from jsonb_to_record(&#39;{&quot;ia&quot=
;:[1,2,3]}&#39;::jsonb) as r (ia integer[]);</div><div><br></div><div>There=
 is a gap for arrays.=C2=A0 Scalars you can just cast and composites have t=
hese functions.=C2=A0 But no simple/direct way to go from json array to sql=
 array is presently implemented.</div><div><br></div><div>Though since 17 j=
son_query can apparently do it.</div><div><br></div><div>select pg_typeof( =
json_query(&#39;[1,2,3]&#39;::jsonb, &#39;$&#39; returning integer[]) ) -&g=
t; integer[]</div><div><br></div><div><br></div><div>David J.</div><div><br=
></div>
</blockquote></div>

--000000000000c249a6063804108d--