public inbox for [email protected]  

help / color / mirror / Atom feed

From: Phillip Diffley <[email protected]>
To: [email protected]
Subject: Convert JSON value back to postgres representation
Date: Thu, 19 Jun 2025 23:05:35 +0200
Message-ID: <CAGAwPgSCTk=HWKtrzf4L+DOdCFDNOZY0Np9go0nsT6wqe6jd=w@mail.gmail.com> (raw)

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

view thread (3+ messages)  latest in thread

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]
  Subject: Re: Convert JSON value back to postgres representation
  In-Reply-To: <CAGAwPgSCTk=HWKtrzf4L+DOdCFDNOZY0Np9go0nsT6wqe6jd=w@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