On Thursday, June 19, 2025, Laurenz Albe <laurenz.a...@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.