Convert JSON value back to postgres representation

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

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.

Good to know. Thank you!

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