javascriptsnowflake-cloud-data-platformsnowflake-schema

Invalid argument type in javascript snowflake function


I have a javascript function in snowflake:

create or replace function array_to_dict(ARR array, KEY_FIELD_NAME string, VALUE_FIELD_NAME string)
returns variant
language javascript
as $$
  return Object.fromEntries(ARR.map(_ => ([_[KEY_FIELD_NAME], _[VALUE_FIELD_NAME]])))
$$;

it takes array of dict and returns dict.

Example input:

[ { "key": "country_code", "value": "ZA" } ]

output:

{'country_code': 'ZA'}

I am calling it as :

select array_to_dict_test(params)

Earlier params was of type variant and the function was working fine, now the type has been changed to ARRAY(OBJECT(key VARCHAR(16777216), value VARCHAR(16777216)))

and it is throwing me exception:

Invalid argument types for function 'ARRAY_TO_DICT': (ARRAY(OBJECT(key VARCHAR(16777216), value VARCHAR(16777216))), VARCHAR(3), VARCHAR(5))


Solution

  • Params data type should match the signature of the function:

    create function array_to_dict_test(arr ARRAY(OBJECT(key TEXT, value TEXT)))
    returns variant
    language javascript
    as $$
      return Object.fromEntries(ARR.map(_ => ([_["key"], _["value"]] )))
    $$;
    

    Call:

    select array_to_dict_test(
        [{'key':'country_code','value':'ZA'}]::ARRAY(OBJECT(key TEXT, value TEXT)));
    

    Output:

    enter image description here


    Side note: The same effect can be achieved with SQL only approach by reducing an array:

    SELECT [{'key':'country_code','value':'ZA'}] AS params,
        REDUCE(params, {}, (acc,x) -> OBJECT_INSERT(acc, x:key, x:value))
    

    Output:

    enter image description here


    EDIT:

    The signature is array_to_dict(ARR array, KEY_FIELD_NAME string, VALUE_FIELD_NAME

    Then:

    create or replace function array_to_dict_test(arr ARRAY, key_field_name TEXT, value_field_name TEXT)
      returns variant
    language javascript
    as $$
      return Object.fromEntries(ARR.map(_ => ([_[KEY_FIELD_NAME], _[VALUE_FIELD_NAME]])))
    $$;
    
    select array_to_dict_test(
        arr=>[{'key':'country_code','value':'ZA'}],
        key_field_name=>'key',
        value_field_name=>'value');
    -- {"country_code": "ZA"}