CODEWITHSUNDEEP
sqljsonpostgresqlcasting
buddemat
buddemat

Reputation: 5311

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.

Upvotes: 1

Views: 2310

Answers (1)

buddemat
buddemat

Reputation: 5311

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.

Upvotes: 4

Related Questions