2

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"

MAK
  • 6,824
  • 25
  • 74
  • 131

1 Answers1

4

The ::int cast is applied to 'id' because it has a higher precedence.

select (json_array_elements(id)->>'id')::int ids 
from tbl_json;