How to cast postgres JSON column to int without key being present in JSON (simple JSON values)?

I am working on data in postgresql as in the following mytable with the fields id (type int) and val (type json):

id	val
1	"null"
2	"0"
3	"2"

The values in the json column val are simple JSON values, i.e. just strings with surrounding quotes and have no key.

I have looked at the SO post How to convert postgres json to integer and attempted something like the solution presented there

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

but in my case, I do not have a key to address the field and leaving it empty does not work:

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

This returns NULL for all rows.

The best I have come up with is the following (casting to varchar first, trimming the quotes, filtering out the string "null" and then casting to int):

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

which works, but surely cannot be the best way to do it, right?

Here is a db<>fiddle with the example table and the statements above.

Solution

Found the way to do it:

You can access the content via the keypath (see e.g. this PostgreSQL JSON cheatsheet):

Using the # operator, you can access the json fields through the keypath. Specifying an empty keypath like this {} allows you to get your content without a key.
Using double angle brackets >> in the accessor will return the content without the quotes, so there is no need for the trim() function.

Overall, the statement

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

will return the contents of the former json column as int, respectvely NULL (in postgresql >= 9.4):

id	val_int
1	NULL
2	0
3	2

See updated db<>fiddle here.

Note: As pointed out by @Mike in his comment above, if the column format is jsonb, you can also use val->>0 to dereference scalars. However, if the format is json, the ->> operator will yield null as result. See this db<>fiddle.