json_extract_path_text cast to int error

Question

I'm trying to extract an integer from a JSON blob stored in a text field in amazon redshift. My query looks (roughly) like:

select json_extract_path_text(json_column, 'integer_field')::int from data;

However, I'm getting a strange error:

ERROR: Invalid digit, Value '1', Pos 0, Type: Integer

Why is '1' an invalid digit type? What is happening here?

Note that I'm casting this to int because json_extract_path_text returns a string. — DaveA
– DaveA, Commented Sep 2, 2014 at 22:20

Community · Accepted Answer · 2017-05-23 11:44:33Z

8

It turns out (thanks to this StackOverflow question) that when there is no 'integer_field' in the JSON blob, the value returned is the empty string ''. For some reason, that is not supported by the cast to integer function. Weirdly enough, a non-empty string ' ' is just fine as input, so the following workaround solved the problem:

nullif(json_extract_path_text(json_column, 'integer_field'), ' ')::int

And now we know.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Sep 2, 2014 at 22:24

DaveA

1,3073 gold badges14 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

json_extract_path_text cast to int error

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