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

--000000000000207b210637f9fc24
Content-Type: text/plain; charset="UTF-8"

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 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.

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

On Thursday, June 19, 2025, Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe=
@cybertec.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br><blockquote class=
=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padd=
ing-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;:<wbr>: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>

--000000000000207b210637f9fc24--