How to convert postgres json to integer

April 16, 2023 by Tarik

What works for me (using posgtgresql 5.6) is

SELECT (tablename.jsoncolumnname->>'jsonfiledname')::int FROM tablename;

SELECT (users.data->>'failed_login_attempts_count')::int FROM users;

Assuming users table has a json column named data which is something like:

{"failed_login_attempts_count":"2","comment":"VIP"}