Cast from json to int not working?

Question

I need to somehow cast from json to int. I have made this func.:

CREATE OR REPLACE FUNCTION json2int(p_json json)
  RETURNS integer AS
$BODY$DECLARE
  v_json json;
  --v_char character varying;
  v_int integer;
BEGIN
  SELECT p_json->'additionalData'->'id' INTO v_json;
  --SELECT CAST(v_json as character varying) INTO v_char;
  SELECT CAST(v_json as integer) INTO v_int;
  RETURN v_int;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION json2int(json)
  OWNER TO postgres;

I tried casting from json to int, that did't work. Then I tried casting from json to character varying to int, that also did't work. So I added:

CREATE CAST (json AS integer) WITH INOUT as implicit;

And now when I run my function as:

SELECT json2int('{"additionalData":{"id":"4","userType":"viewer"},"type":"wall"}');

I get this error:

ERROR:  invalid input syntax for integer: ""4""
CONTEXT:  SQL statement "SELECT CAST(v_json as integer)"
PL/pgSQL function json2int(json) line 8 at SQL statement

Can somebody help with this?

There are 2 functions to get values from json: `->` and `->>`. One returns JSON, the other one - text. You need the one, that returns text. — Ihor Romanchenko, Sep 12 '14 at 14:15
Details here: http://www.postgresql.org/docs/current/static/functions-json.html — Ihor Romanchenko, Sep 12 '14 at 14:16

score 9 · Answer 1 · answered Sep 12 '14 at 14:44

9

As hinted in the comments use ->> to have text then cast to int:

SELECT (p_json -> 'additionalData' ->> 'id')::int INTO v_int;

answered Sep 12 '14 at 14:44

Clodoaldo Neto

118,695
26
233
260

that just created a table called v_int – Ivan Yulin Apr 14 '22 at 11:16

Cast from json to int not working?

1 Answers1