public inbox for [email protected]
help / color / mirror / Atom feedConvert JSON value back to postgres representation
3+ messages / 3 participants
[nested] [flat]
* Convert JSON value back to postgres representation
@ 2025-06-19 21:05 Phillip Diffley <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Phillip Diffley @ 2025-06-19 21:05 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Convert JSON value back to postgres representation
@ 2025-06-20 04:59 Laurenz Albe <[email protected]>
parent: Phillip Diffley <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2025-06-20 04:59 UTC (permalink / raw)
To: Phillip Diffley <[email protected]>; pgsql-general
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.
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
═════════
{1,2,3}
(1 row)
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Convert JSON value back to postgres representation
@ 2025-06-20 05:17 David G. Johnston <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2025-06-20 05:17 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Phillip Diffley <[email protected]>; pgsql-general
On Thursday, June 19, 2025, Laurenz Albe <[email protected]> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-06-20 05:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-19 21:05 Convert JSON value back to postgres representation Phillip Diffley <[email protected]>
2025-06-20 04:59 ` Laurenz Albe <[email protected]>
2025-06-20 05:17 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox