public inbox for [email protected]  

help / color / mirror / Atom feed

From: Phillip Diffley <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Convert JSON value back to postgres representation
Date: Fri, 20 Jun 2025 19:19:20 +0200
Message-ID: <CAGAwPgRqSZbbsS1bS891dgyEsYZGt1eN=aSiHFYxRYM0MY6hvA@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwZJCdJZAAJJnmU8cwiD=-=dLcnaRfJ-LxAL8zdH5Ft3QA@mail.gmail.com>
References: <CAGAwPgSCTk=HWKtrzf4L+DOdCFDNOZY0Np9go0nsT6wqe6jd=w@mail.gmail.com>
	<[email protected]>
	<CAKFQuwZJCdJZAAJJnmU8cwiD=-=dLcnaRfJ-LxAL8zdH5Ft3QA@mail.gmail.com>

Good to know. Thank you!

On Fri, Jun 20, 2025 at 7:17 AM David G. Johnston <
[email protected]> wrote:

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Convert JSON value back to postgres representation
  In-Reply-To: <CAGAwPgRqSZbbsS1bS891dgyEsYZGt1eN=aSiHFYxRYM0MY6hvA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox