1

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.

buddemat
  • 4,552
  • 14
  • 29
  • 49
  • There might be a better way, but I dereference naked json/jsonb scalars with `val->>0`. – Mike Organek Aug 08 '22 at 19:12
  • Thank you @Mike, but wouldn't that only work if the values were inside an array? In my tests, `val->>0` returned `NULL` for all my rows. – buddemat Aug 08 '22 at 20:58
  • That is strange. Here is what I get in PostgreSQL 10 and 14: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f7dc781ade9125b923e2c99b6c4dc561 – Mike Organek Aug 09 '22 at 11:08
  • I think the difference is that in your example, the type is `jsonb`, in mine it's `json`. Your approach only seems to work with `jsonb`, whereas the one below for both: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=6061e88c05ef86a99402d3a9cd99af93 I'll update my answer to incorporate that info. Thanks! – buddemat Aug 10 '22 at 09:15

1 Answers1

2

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.

buddemat
  • 4,552
  • 14
  • 29
  • 49