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[]));
Thank you,
Phillip
