snowflake-cloud-data-platform

Retain Numeric Precision Using OBJECT_CONSTRUCT

I have a currency value in a table that I want to include in an OBJECT. The problem is that it looks like Snowflake drops the 2 decimal place precision I want to keep when converting to an OBJECT.

Example:

select to_number('124.00', 18, 2) desired_output, object_construct('my_key', to_number('124.00', 18, 2)) incorrect_output;

DESIRED_OUTPUT  INCORRECT_OUTPUT
124.00  { "my_key": 124 }

DESIRED_OUTPUT is keeping the precision I want. But as soon as I try to set it as a value in the object, it gets dropped. Is there a way to keep the format without converting it to a VARCHAR?

Solution

  • I submitted a ticket to snowflake support about this and they told me that object_construct "automatically converts numbers to their most compact representation, which means it will drop trailing zeros after the decimal point for integer values." I'm not sure what benefit this has, but the two solutions they gave were either:

    1. Convert the value to a string with TRIM(TO_CHAR(TO_NUMBER(100, 18, 2), '9999999999999999D00'))
    2. Manually construct the json like this SELECT '{"fieldname": ' || TO_NUMBER(100, 18, 2) || '}';

    (100 is just used as a static value for reproducing the problem) Number 2 seems like a really hacky solution to me. I ended up going with option 1 since this output is being loaded into opensearch and for some reason, opensearch accepts strings when the field has a type mapping to float even though it doesn't accept integers.