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

Question

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.

There might be a better way, but I dereference naked json/jsonb scalars with val->>0. — Mike Organek
– Mike Organek, Commented Aug 8, 2022 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
– buddemat, Commented Aug 8, 2022 at 20:58
That is strange. Here is what I get in PostgreSQL 10 and 14: dbfiddle.uk/… — Mike Organek
– Mike Organek, Commented Aug 9, 2022 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: dbfiddle.uk/… I'll update my answer to incorporate that info. Thanks! — buddemat
– buddemat, Commented Aug 10, 2022 at 9:15

buddemat · Accepted Answer · 2022-08-10 09:21:32Z

4

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.

edited Aug 10, 2022 at 9:21

answered Aug 8, 2022 at 18:45

buddemat

5,33016 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related