Postgres 9.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 9.4.26 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit

SELECT 1

CREATE TABLE mytable (id int, val json);
INSERT INTO mytable values (1, '"null"');
INSERT INTO mytable values (2, '"0"');
INSERT INTO mytable values (3, '"2"');
SELECT * FROM mytable;

id
val

1
"null"

2
"0"

3
"2"

SELECT 3

SELECT (mytable.val->>'')::int as val_int FROM mytable;

val_int

null

null

null

SELECT 3

SELECT id, nullif(trim('"' from mytable.val::varchar), 'null')::int as val_int FROM mytable;

id
val_int

1
null

2
0

3
2

SELECT 3

select id
     , nullif(val#>>'{}', 'null')::int as val_int
  from mytable
;

id
val_int

1
null

2
0

3
2

SELECT 3

val_int
null
null
null

id	val_int
1	null
2	0
3	2

id	val_int
1	null
2	0
3	2