Extract negative integers from JSON

by the_darkside   Last Updated October 10, 2019 20:26 PM

I am using the json_extract function in PrestoSQL, however, if the key-value pair appears with a negative integer in the value such as

{"foo":-12345, "bar": 12345}

json_extract(json, '$.foo') will return NULL but

json_extract(json, '$.bar') will return 12345

json_extract_scalar also produces the same.

What is the workaround for extracting negative integers in Presto?

Answers 1

As per the documentation, json_extract() returns a JSON string. You could try and CAST it to a number, like:

CAST(json_extract(json, '$.foo') AS INTEGER)

Another alternative would be to use json_extract_scalar():

json_extract_scalar(json, '$.foo')


CAST(json_extract_scalar(json, '$.foo') AS INTEGER)

Unfortunately there is no PrestoDB fiddle for me to test this.

October 10, 2019 20:18 PM

Related Questions

Updated May 14, 2019 03:26 AM

Updated February 10, 2019 18:26 PM

Updated May 11, 2017 22:26 PM

Updated February 20, 2018 21:26 PM