JSON structure formed by arrays within arrays-CodePudding

I'm parsing this JSON with the aim of exporting the list of events row by row to the sheet. This JSON has a structure that I consider not simple, there are arrays within arrays.

I expect something like this

03/07/21    Rugby League    NRL Canterbury-Bankstown    Manly   Ended   0   66
03/07/21    Rugby League    NRL Canberra    Gold Coast  Ended   6   44
03/07/21    Rugby League    NRL Newcastle   North Queensland    Ended   0   38
03/07/21    Rugby Union International Friendlie Maori All Blacks    Samoa   Ended   38  21
... further events

This has been my approach

function jSON() {
  
  var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("jsonTest");
  var response = UrlFetchApp.fetch("https://lsc.fn.sportradar.com/common/en/Europe:London/gismo/sport_matches/12/2021-07-03");
  var dataRes  = JSON.parse(response.getContentText());
  var dataSet = dataRes.doc[0].data.sport.realcategories;
  var output = []  
  for (var i = 0; i < dataSet.length; i  ) {
    data = dataSet[i];
    var val = [];
    val = [
      data.tournaments[0].matches[i]._dt.date
      ,data.name
      ,data.tournaments[0].name
      ,data.tournaments[0].matches[i].teams.home.name
      ,data.tournaments[0].matches[i].teams.away.name
      ,data.tournaments[0].matches[i].status.name
      ,data.tournaments[0].matches[i].result.home
      ,data.tournaments[0].matches[i].result.away
    ];
    
    output.push(val);
    Logger.log(output);
  }
  
  destSheet.getRange(2,1,output.length,output[0].length).setValues(output);          
}

I got this output

03/07/21    Rugby League    NRL Canterbury-Bankstown    Manly   Ended   0   66
03/07/21    Rugby Union International Friendlies    New Zealand Tonga   Ended   102 0

I'd like to try to catch all events and use the correct approach.

CodePudding user response：

Basically you need more loops; one for each nested array to get into it.

There are a few issues; watch your code indenting it makes it hard to read and easy to make mistakes. Also you are using i which is the realcategories counter on matches (matches[i]) meaning you are only looking at match 0 of category 0 and match 1 of category one...

Try this: the other answer is more modern, but this is closer to where you're starting; so hopefully makes sense...?

for (var i = 0; i < dataSet.length; i  ) {
    data = dataSet[i];
    for (var j = 0; j < data.tournaments.length; j  ) {
        for (var k = 0; k < data.tournaments[j].matches.length; k  ) {
            output.push([
                 data.tournaments[j].matches[k]._dt.date
                ,data.name
                ,data.tournaments[j].name
                ,data.tournaments[j].matches[k].teams.home.name
                ,data.tournaments[j].matches[k].teams.away.name
                ,data.tournaments[j].matches[k].status.name
                ,data.tournaments[j].matches[k].result.home
                ,data.tournaments[j].matches[k].result.away
            ]);
        }
    }
}

CodePudding user response：

You need to iterate over categories, tournaments and matches. Maybe something like this:

var response = UrlFetchApp.fetch("https://lsc.fn.sportradar.com/common/en/Europe:London/gismo/sport_matches/12/2021-07-03");
var dataRes  = JSON.parse(response.getContentText());
var dataSet = dataRes.doc[0].data.sport.realcategories; 

dataSet.reduce((prev, category) => {
  return [
    ...prev,
    ...category.tournaments
      .map((tournament) => {
        return tournament.matches.map((match) => {
          return [
            match._dt.date,
            category.name,
            tournament.name,
            match.teams.home.name,
            match.teams.away.name,
            match.status.name,
            match.result.home,
            match.result.away,
          ];
        });
      })
      .flat(),
  ];
}, []);