From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Phillip Diffley <phillip6402(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Convert JSON value back to postgres representation |
Date: | 2025-06-20 04:59:41 |
Message-ID: | b9ffb897998ec715ff5ced0204e9f89631d260da.camel@cybertec.at |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2025-06-20 05:17:55 | Re: Convert JSON value back to postgres representation |
Previous Message | Phillip Diffley | 2025-06-19 21:05:35 | Convert JSON value back to postgres representation |