Error while parsing, when loading the data from snowflake having array datatype

Question

I am trying to read data from snowflake and load it to other storage location. I am using snowflake jdbc driver 3.12.9 to read the data and jackson library(2.10) to deserialize it.

In one of the usecase, I am using column with array datatype. Here is the create table statement : create table test_table (array1 ARRAY). I am loading the data into table by following command. insert into test_table (array1) select array_construct(1,2,3,null). When we run the select query on above data null value in the array is getting converted as undefined. e.g. select array1 from test_table, we are getting output as "[1, 2, 3, undefined]"

While deserializeing the data with jackson, I am getting error : Unrecognized token 'undefined': was expecting (JSON String, Number, Array, Object or token 'null', 'true' or 'false'), as undefined is not a valid json token.

Conversion of null to undefined is happening only when one of the array element is null and not happening in case of entire array is null.

One of the solution is to write custom function at snowflake side and use it while querying the data but then we have to add this function at all our customers end and we don't want add this to be a blocker.

Is there any better way to solve this problem. Any suggestions, help will be very helpful.

Answer 1

I couldn't find an easy answer for this. So, I had to replace null using COALESCE when constructing the array, like:

select to_json(array_construct(coalesce(col1, '"null"'), coalesce(col2, '"null"')))