JS data transformation

Hi Retool Team,

I am not very versed in JS and would like to seek help the JS for the following transformation

input = [
{index: 1, location: HKG, product: Food},
{index: 1, location: HKG, product: Wood},
{index: 1, location: HKG, product: Clothing},
{index: 2, location: SGH, product: Food},
{index: 2, location: SGH, product: Wood},
{index: 2, location: SGH, product: Clothing}

output = [
{index: 1, location: HKG, product: [Food, Wood, Clothing]},
{index: 2, location: SGH, product: [Food, Wood, Clothing]

My code below fails to execute and returns "equipment.push() is not a function"

let max_index = _.max(input.index)
let input = _.orderBy(input, 'index', 'asc')
let output = [], product = [], location=[]
while (i <= max_index) {
for (let item of input){
if (item.index == i) {
product = _.uniq(product)
location = _.uniq(location).toString()
index: i,
product: product,
return output

Hey @oliver.ng!

Happy to help here! I believe the best approach here would be to loop through all records and split this logic into two categories:

  1. If you haven't seen this particular record index before, we add its index to our seen indices array and add the record to an object using its index as a key
  2. If you have seen this particular record index before, we want to use its index to find the matching record and add the product to its array of products.

Lastly, we would need to reformat the object to an array using Object.values, your code could look something like this below:

let input = inputQuery.value
let output = {}
let seenIndices = []

input.forEach(record => {
if (!seenIndices.includes(record.index)) {
// if records index does not exist in seenIndices add it to seenIndices and output
output[record.index] = Object.assign(record, {"product": [record.product]});
} else {
// if records index does exist add its product to the same index

return Object.values(output)

Hope this helps, let me know if you have any questions! :)

Thank you so much for this Chris