I have the following sample data for demo:
Table:
create table tbl_json
(
id json
);
Some values:
insert into tbl_json values('[{"id":1},{"id":2},{"id":3}]');
Query: Convert/cast id into integer from json column.
Tried:
select json_array_elements(id)->>'id'::int ids
from tbl_json;
Getting an error:
ERROR: invalid input syntax for integer: "id"
The ::int cast is applied to 'id' because it has a higher precedence.
select (json_array_elements(id)->>'id')::int ids
from tbl_json;