How to convert postgres json to integer

How to convert postgres json to integer

I can use to_json(1) to cast int to json, but how can I convert json to int? This may be too slow:

to_json(1)::text::int

Also, is json wrapped from a binary block (bson) or a simple wrapper of text?

What works for me (using posgtgresql 5.6) is

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

like

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"}

to_json(1)::text::int maybe too slow

But then, it's the only way.

The second part of your question is unclear.

The PostgreSQL 9.3 JSON support is simply validated json text.

In 9.4 and newer you can use jsonb.

"may be too slow" doesn't make a ton of sense. What makes you think it's too slow? Did you test and benchmark? If it's "too slow" what speed would not be too slow, i.e. what do you expect?

ReferenceURL : https://stackoverflow.com/questions/20236421/how-to-convert-postgres-json-to-integer