1

It looks like Snowflake UDFs emit decimal values within object types (JSON structures) using scientific notation by default, even if the values don't have much precision or many significant digits. Is there a way to prevent this transformation, or is there some way to modify the UDF to emit the "simpler" decimal value?

I am looking to preserve the object structure, so returning a simple scalar decimal value would not fit this use case (in other words, returning some simple value and calling TO_DECIMAL() or similar with some formatting options would not be an appropriate solution--unless there is a creative use for it).

Also, for this use case, it's necessary to pass in the entire object because I'm performing some recursive processing on it, so simply selecting the scalar decimal value by JSON path would not fit either.

UDF definition:

CREATE OR REPLACE FUNCTION F_DECIMAL(INPUT VARIANT)
RETURNS VARIANT
LANGUAGE javascript
AS
'
    return INPUT;
'

Query:

SELECT
    PARSE_JSON('{"a": 1234.56789012345678901234, "b": 2.0, "c": 3, "d": 1739210.5, "e": 1234.567}') AS json_parsed_simple,
    F_DECIMAL(PARSE_JSON('{"a": 1234.56789012345678901234, "b": 2.0, "c": 3, "d": 1739210.5, "e": 1234.567}')) AS json_udf

Result:

JSON_PARSED_SIMPLE JSON_UDF
{ "a": 1234.56789012345678901234, "b": 2, "c": 3, "d": 1739210.5, "e": 1234.567 } { "a": 1.234567890123457e+03, "b": 2, "c": 3, "d": 1.739210500000000e+06, "e": 1.234567000000000e+03 }
happens
  • 13
  • 3

1 Answers1

0

There's no guarantee this will always work, but returning a stringified object and casting back to an object using ::object in the SQL gets rid of the scientific notation in this case:

CREATE OR REPLACE FUNCTION F_DECIMAL(INPUT VARIANT)
RETURNS variant
LANGUAGE javascript
AS
'
    return JSON.stringify(INPUT);
';

SELECT
    PARSE_JSON('{"a": 1234.56789012345678901234, "b": 2.0, "c": 3, "d": 1739210.5, "e": 1234.567}') AS json_parsed_simple,
    F_DECIMAL(PARSE_JSON('{"a": 1234.56789012345678901234, "b": 2.0, "c": 3, "d": 1739210.5, "e": 1234.567}'))::object AS json_udf

Returns:

{
  "a": 1234.567890123457,
  "b": 2,
  "c": 3,
  "d": 1739210.5,
  "e": 1234.567
}
Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
  • Thank you; this works pretty well! I may have a follow-up as I described only a particular part of the general use case, but this definitely helps getting over this hurdle. – happens Sep 30 '21 at 20:14