How to convert array object in snowflake

Question

I would like to convert the below array of objects into another form (varname is ignored as its not required, key and value is used to generate the output form). Any leads would be appreciated

Input array: [ { "key": "string_U6", "value": "grandwagoneer", "varname": "pagenameplate" }, { "key": "string_U13", "value": "2021", "varname": "year" } ]

Output

[ { "string_U6": "grandwagoneer" }, { "string_U13": "2021" } ]

Answer 1

You could try using map as below:

var input = [ { "key": "string_U6", "value": "grandwagoneer", "varname": "pagenameplate" }, { "key": "string_U13", "value": "2021", "varname": "year" } ];

var output = input.map(function(entry){
    let obj = {}; 
    obj[entry.key] = entry.value;
    return obj;
});

console.log(output);

Answer 2

As the question asked how to convert array object in snowflake, I wanted to share Snowflake way to do it:

-- SQL to create a sample table with data

create table sample_table (v variant ) 
as select parse_json(' [ { "key": "string_U6", "value": "grandwagoneer", "varname": "pagenameplate" },
{ "key": "string_U13", "value": "2021", "varname": "year" } ]');
    
-- query to parse the variant and create the array:

select ARRAY_AGG( OBJECT_CONSTRUCT(i.value:key::varchar, i.value:value::varchar) )  
from sample_table,
lateral flatten ( sample_table.v ) i;

It will produce exact output you want.