65

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?

Brad Koch
  • 19,267
  • 19
  • 110
  • 137
Inshua
  • 1,355
  • 1
  • 12
  • 12
  • Related: [How to convert Postgres json(b) to float?](https://stackoverflow.com/q/24826385/1048572), [to text?](https://stackoverflow.com/q/27215216/1048572), [to boolean?](https://stackoverflow.com/q/33041617/1048572) – Bergi Jul 07 '22 at 00:25
  • 100::text::jsonb – Time Killer Apr 13 '23 at 02:49

3 Answers3

78

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"}
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
Ali
  • 18,665
  • 21
  • 103
  • 138
  • 2
    SELECT (*)::int - That's what I was looking for! Thank you. – Junaid Atari Aug 17 '18 at 22:23
  • 1
    `(ta.args ->> 'deposit')::bigint` seemed like it was going to work, but I got `ERROR: value "1830000000000000000000" is out of range for type bigint` – Ryan Feb 04 '22 at 19:51
  • Ryan, these answers should help you for sure. the problem is, you just cannot store that big number as integer, because then you lack efficiency in using integers as data types. https://stackoverflow.com/questions/7142604/what-to-do-when-you-need-integers-larger-than-20-digits-on-mysql – Mochi Feb 15 '22 at 15:02
20

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

But then, it's the only way.

The second part of your question is unclear.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
8

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?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • thank you, hope the new version published soon. since there is a transform between string and number, it will certainly slow than direct integer type, as everybody know. thow it can be accept in real-case useage, we are seeking more fast way. i put this question under json-to-int question, because i'd suspected json is wrapped from text, this let json-to-int must cast from text,otherwise,it's store structure should be already typed. – Inshua Nov 27 '13 at 09:06
  • `jsonb` now exists. Does it support directly extracting integers from json? – Tom Ellis Dec 19 '14 at 13:49
  • @Craig Ringer - it has problems if value is null: select ('[null]'::json->0)::text::int raises invalid input syntax for integer: "null" which makes sense ... this works: select ('[null]'::json->>0)::int ... returns null – Reinsbrain Sep 26 '16 at 14:14