CODEWITHSUNDEEP
javascriptsqlstored-proceduressnowflake-cloud-data-platform
alim1990
alim1990

Reputation: 4972

How to fetch all data into splitted row into array of objects in Snowflake?

I am having this procedure in which we specify the path to the stage file and which file format should be used, then we get the first 2 columns of it and return it as a variant of type array having the following structure:

result = [
    { dim_label_name: 'Test1', dim_ona_name: 'Ona Test1' },
    { dim_label_name: 'Test2', dim_ona_name: 'Ona Test2' },
]

Here is the procedure:

CREATE OR REPLACE PROCEDURE ADD_FIELD_AND_DIMENSION(FILE_FULL_PATH STRING, FILE_FORMAT_NAME STRING)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    var dimension_array = [];
    try{
      // Get the unique values of the available dimensions
      var unique_dimension_query = "SELECT t.$1, t.$2 FROM "+FILE_FULL_PATH+"(FILE_FORMAT=>"+FILE_FORMAT_NAME+") t GROUP BY t.$1, t.$2";
      var unique_dimension_stmt = snowflake.createStatement({sqlText: unique_dimension_query});
      var rs = unique_dimension_stmt.execute();
      var dimension_object = {};
      while (rs.next()) {
        dimension_object['dim_label_name'] += rs.getColumnValue(1);
        dimension_object['dim_ona_name'] += rs.getColumnValue(2);
      }
      dimension_array.push(dimension_object);
      return dimension_array;
    }
    catch(error) {
        return error;
    }
$$;

CALL ADD_FIELD_AND_DIMENSION('@ingest_stage_temp/my_file.csv', 'my_file_format');

The returned dimension_array is having the structure of only one object having all values fitted to each property:

[
    { 'dim_label_name': 'Test1Test2Test3...', 'dim_ona_name': 'ONA Test1 ONA Test2 ONA Test3...' }
]

How to fetch all data into splitted row into array of objects in Snowflake?

Upvotes: 0

Views: 394

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

Your push to the dimension_array is outside your loop collecting the result set. It's only going to push the last row in the result set to the array.

Also, you want to assign the strings to the elements of the array using =. The += is concatenating the values to the string elements, which is why they're all together in one string.

This part has the problems:

  while (rs.next()) {
    dimension_object['dim_label_name'] += rs.getColumnValue(1);
    dimension_object['dim_ona_name'] += rs.getColumnValue(2);
  }
  dimension_array.push(dimension_object);

It should be like this:

  while (rs.next()) {
    dimension_object['dim_label_name'] = rs.getColumnValue(1);
    dimension_object['dim_ona_name'] = rs.getColumnValue(2);
    dimension_array.push(dimension_object);
  }

Upvotes: 1

Related Questions