From: | Phillip Diffley <phillip6402(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Convert JSON value back to postgres representation |
Date: | 2025-06-19 21:05:35 |
Message-ID: | CAGAwPgSCTk=HWKtrzf4L+DOdCFDNOZY0Np9go0nsT6wqe6jd=w@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2025-06-20 04:59:41 | Re: Convert JSON value back to postgres representation |
Previous Message | Pavel Luzanov | 2025-06-19 20:01:48 | Re: does multi host connection string supports login event trigger exception |