CODEWITHSUNDEEP
snowflake-cloud-data-platform
jwlon81
jwlon81

Reputation: 359

How to add fields dynamically to snowflake's object_construct function

I have a large table of data in Snowflake that contains many fields with a name prefix of 'RAW_'. In order to make my table more manageable, I wish to condense all of these 'RAW_' fields into just one field called 'RAW_KEY_VALUE', condensing all of it into a key-value object store.

It initially appeared that Snowflake's 'OBJECT_CONSTRUCT' function was going to be my perfect solution here. However, the issue with this function is that it requires a manual input/hard coding of the fields you wish to convert to a key-value object. This is problematic for me as I have anywhere from 90-120 fields I would need to manually place in this function. Additionally, these fields with a 'RAW_' prefix change all the time. It is therefore critical that I have a solution that allows me to dynamically add these fields and convert them to a key-value store. (I haven't tried creating a stored procedure for this yet but will if all else fails)

Here is a snippet of the data in question

create or replace table reviews(name varchar(50), acting_rating int, raw_comments varchar(50), raw_rating int, raw_co varchar(50));

insert into reviews values
    ('abc', 4, NULL, 1, 'NO'),
    ('xyz', 3, 'some', 1, 'haha'),
    ('lmn', 1, 'what', 4, NULL);

Below is the output I'm trying to achieve (using the manual input/hard coding approach with object_construct)

select 
name , 
acting_rating ,
object_construct_keep_null ('raw_comments',raw_comments,'raw_rating',raw_rating,'raw_co',raw_co) as RAW_KEY_VALUE
from reviews;

The above produces this desired output below.

enter image description here

Please let me know if there are any other ways to approach here. I think if I was able to work out a way to add the relevant fields to the object_construct function dynamically, that would solve my problem.

Upvotes: 2

Views: 1244

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59275

You can do this with a JS UDF and object_construct(*):

create or replace function obj_with_prefix(PREFIX string, A variant)
returns variant
language javascript
as $$
let result = {};

for (key in A) {
  if (key.startsWith(PREFIX))
      result[key] = A[key];
}

return result
$$
;

Test:

with data(aa_1, aa_2, bb_1, aa_3) as (
    select 1,2,3,4
)


select obj_with_prefix('AA', object_construct(*))
from data

enter image description here

Upvotes: 1

Related Questions