json - Updating integer column from jsonb member fails with: column is of type integer but expression is of type jsonb -

in postgresql 9.5 table have integer column social.

when try update in stored procedure given following json data (an array 2 objects, each having "social" key) in in_users variable of type jsonb:

'[{"sid":"12345284239407942","auth":"ddddc1808197a1161bc22dc307accccc",**"social":3**,"given":"alexander1","family":"farber","photo":"https:\/\/graph.facebook.com\/1015428423940942\/picture?type=large","place":"bochum, germany","female":0,"stamp":1450102770},   {"sid":"54321284239407942","auth":"ddddc1808197a1161bc22dc307abbbbb",**"social":4**,"given":"alxander2","family":"farber","photo":null,"place":"bochum, germany","female":0,"stamp":1450102800}]'::jsonb 

then following code failing:

    t in select * jsonb_array_elements(in_users)     loop             update words_social set                     social = t->'social',             sid = t->>'sid';     end loop; 

with error message:

error:  column "social" of type integer expression of type jsonb line 3:                         social = t->'social',                                          ^ hint:  need rewrite or cast expression. 

i have tried changing line to:

social = t->'social'::int, 

but error:

error:  invalid input syntax integer: "social" line 3:                         social = t->'social'::int,                                             ^ 

why doesn't postgresql recognize data integer?

from json-type-mapping-table having impression json number auto-converted postgresql numeric type.

a single set-based sql command far more efficient looping:

update words_social w set    social = (iu->>'social')::int   jsonb_array_elements(in_users) iu  -- in_user = function variable  w.sid = iu->>'sid';                -- type of sid? 

to answer original question:

why doesn't postgresql recognize data integer?

because trying convert jsonb value integer. in solution found need ->> operator instead of -> extract text, can cast integer.

your second attempt added second error:

t->'social'::int

in addition above: operator precedence. cast operator :: binds stronger json operator ->. found already, want:

(t->>'social')::int 

very similar case on dba.se:

• querying jsonb in postgresql