How to transform a data which is list of dictionaries to a dictionary of dictionaries with key being one of the value in inner dicts in Snowflake

Question

How to transform a data which is list of dictionaries to a dictionary of dictionaries with key being one of the value in inner dicts in Snowflake.

Context: The values I want to transform are in a cell of a table, i am trying to replace that cell with a transformed format.

I am trying to find ways to the task above in two ways. 1. SnowflakeSQL 2. Javascript UDF This is a value in one of the cells in a table.

[
{
  "Vehicle_type": "P",
  "Vehicle_year": "2022",
  "KEY": "key_98765"
},

{
  "Vehicle_type": "Q",
  "Vehicle_year": "2019",
  "KEY": "key_12345"
}
]

I want this to be transformed to

  {
'key_98765':
{
  "Vehicle_type": "P",
  "Vehicle_year": "2022",
  "KEY": "key_98765"
},
'key_12345':
{
  "Vehicle_type": "Q",
  "Vehicle_year": "2019",
  "KEY": "key_12345"
}

}

user_18376854 · Answer

You can do it like this:

script

let arr = [
  {
    "Vehicle_type": "P",
    "Vehicle_year": "2022",
    "KEY": "key_98765"
  },
  {
    "Vehicle_type": "Q",
    "Vehicle_year": "2019",
    "KEY": "key_12345"
  },
];

let arrWithKeys = [];

arr.forEach((value => {
  arrWithKeys.push({ [value["KEY"]]: value });
}))

console.log(arrWithKeys);

How to transform a data which is list of dictionaries to a dictionary of dictionaries with key being one of the value in inner dicts in Snowflake

1 Answers