Transform array into table

Hi,

I’m making a dashboard for instagram and I’m lost in the public part - city.

I have a json that returns separated by :, and I want to transform it into an array, to put in a table.

Essentially I have:

"value": {
"City1": 1,
"City2": 2,
}

And I want to turn into

{
“audience

* List item

” : [
{
“key” : “value”,
“key2” : “value”
},
{
“key” : “value”,
“key2” : “value”
},
]}

How do I do this through function?

(This is the full json that facebook returns)

[
{
"data": [
{
"name": "audience_city",
"period": "lifetime",
"values": [
{
"value": {
"Ribeirão Prêto, São Paulo (state)": 9,
"São Paulo, São Paulo (state)": 1023,
"São Luís, Maranhão": 15,
"Rio de Janeiro, Rio de Janeiro (state)": 51,
"Juazeiro do Norte, Ceará": 9,
"Itapecerica de Serra, São Paulo (state)": 123,
"Osasco, São Paulo (state)": 71,
"Maceió, Alagoas": 17,
"Porto Alegre, Rio Grande do Sul": 14,
"Campo Grande, Mato Grosso do Sul": 16,
"João Pessoa, Paraíba": 11,
"Jandira, São Paulo (state)": 18,
"Teresina, Piauí": 9,
"Feira de Santana, Bahia": 17,
"Recife, Pernambuco": 28,
"Brasília, Federal District": 16,
"São Carlos, São Paulo (state)": 9,
"Guarulhos, São Paulo (state)": 32,
"Sorocaba, São Paulo (state)": 14,
"Piracicaba, São Paulo (state)": 10,
"São Bernardo do Campo, São Paulo (state)": 17,
"Natal, Rio Grande do Norte": 22,
"Barueri, São Paulo (state)": 24,
"Salvador, Bahia": 35,
"Jaboatão dos Guararapes, Pernambuco": 9,
"Mauá, São Paulo (state)": 12,
"Carapicuíba, São Paulo (state)": 69,
"Fortaleza, Ceará": 32,
"Manaus, Amazonas": 17,
"Nova Iguaçu, Rio de Janeiro (state)": 10,
"Cotia, São Paulo (state)": 156,
"Aracaju, Sergipe": 11,
"Itapevi, São Paulo (state)": 11,
"Francisco Morato, São Paulo (state)": 10,
"Florianópolis, Santa Catarina": 10,
"Embu das Artes, São Paulo (state)": 2806,
"Diadema, São Paulo (state)": 15,
"Belo Horizonte, Minas Gerais": 20,
"São José do Rio Prêto, São Paulo (state)": 11,
"Curitiba, Paraná": 14,
"Santo André, São Paulo (state)": 16,
"Taboão da Serra, São Paulo (state)": 427,
"Belém, Pará": 11,
"Franco da Rocha, São Paulo (state)": 13,
"Campinas, São Paulo (state)": 19
},
"end_time": "2021-12-15T08:00:00+0000"
}
],
"title": "Cidade do público",
"description": "Cidades dos seguidores deste perfil",
"id": "17841400180118451/insights/audience_city/lifetime"
}
]
}
]```

Already did it!

// Code here will run only once, no matter how many input items there are.
// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function

// Loop over inputs and add a new field called 'myNewField' to the JSON of each one
var result = [];
for (item of items) {
  for (data of item.json.data){
    for (values of data.values)
    {
        for (const [key, value] of Object.entries(values.value)) {
          var location = key.split(',')
          result.push({
            "json": {
              "estado": location[1].replace('(state)', '').trim(),
              "cidade": location[0].trim(),
              "total": value
            }
        });
      }
    }
  }
}

result.sort(function(a, b){
  var totalA = a.json.total;
  var totalB = b.json.total;
  if (totalA < totalB) return 1;
  if (totalA > totalB) return -1;
  return 0;
})

// You can write logs to the browser console
console.log('Done!');

return result
6 Likes