Parse data with no header

SimonKing
SimonKing Member
in Connectors

I have an API which pulls in data

{"name":["details"],"name":["details"],"name":["details"],"name":["details"],..

If I MagicParse this I just get ONE long row with these details.

What I am after is 2 columns of Name and Details. I have parsed the data and added the columns titles but how do I reference the data? 

for(var i=0 ; i<jsonRes.length ; i++) {
  datagrid.addCell(jsonRes[i].?);
  datagrid.addCell(jsonRes[i].?);
  datagrid.endRow();
}
Tagged:

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    Can you reformat the JSON incoming, before you handle it?

    Loop through each object in the array, extracting the details from the "name" key and creating a new object with the key "details" associated with the value.

    // Simulating the incoming invalid JSON (you receive this from the API)
    
var invalidJson = [
    
  { "name": ["details1"] },
    
  { "name": ["details2"] },
    
  { "name": ["details3"] },
    
  { "name": ["details4"] }
    
];


    
// Initialize an array to store the reformatted data
    
var validJsonArray = [];


    
// Loop through each entry and reformat it
    
for (var i = 0; i < invalidJson.length; i++) {
    
  validJsonArray.push({
    
    "name": "name", // This assumes "name" is constant
    
    "details": invalidJson[i].name[0] // Extracting the detail from each object
    
  });
    
}


    
// The validJsonArray now contains the reformatted data
    
console.log(validJsonArray);

    After reformatting:
    [
    { "name": "name", "details": "details1" },
    { "name": "name", "details": "details2" },
    { "name": "name", "details": "details3" },
    { "name": "name", "details": "details4" }
    ]

    for (var i = 0; i < validJsonArray.length; i++) {
    
  datagrid.addCell(validJsonArray[i].name); // Adds "name" to the first column
    
  datagrid.addCell(validJsonArray[i].details); // Adds the corresponding details to the second column
    
  datagrid.endRow(); // End the current row
    
}

    If you regularly receive this malformed JSON from the API, you can wrap the reformatting code in a function and call it each time you receive the data.

    function reformatJsonData(invalidJson) {
    
  var validJsonArray = [];

    
  for (var i = 0; i < invalidJson.length; i++) {
    
    validJsonArray.push({
    
      "name": "name",
    
      "details": invalidJson[i].name[0]
    
    });
    
  }

    
  return validJsonArray;
    
}


    
// Usage
    
var reformattedData = reformatJsonData(invalidJson);

    
// Process the reformatted data as needed

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    ArborRose Coach

    The issue in your JSON code seems to be the repeated "name" keys without nesting. To properly reference the data in your loop, you should ensure that each entry in the array is an object with distinct keys for "name" and "details".

    JSON

    [
    
  {"name": "details1", "detail": "detail1"},
    
  {"name": "details2", "detail": "detail2"},
    
  {"name": "details3", "detail": "detail3"}
    
]
    for(var i = 0; i < jsonRes.length; i++) {
    
  datagrid.addCell(jsonRes[i].name); 
    
  datagrid.addCell(jsonRes[i].detail); 
    
  datagrid.endRow(); 
    
}

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • SimonKing
    SimonKing Member

    Hi, Yes that's the problem, without any key I cannot reference the data. This is data coming from a web service so I have no control over the format.

  • ArborRose
    ArborRose Coach

    Given a structure:

    {
    "name": ["details1"],
    "name": ["details2"],
    "name": ["details3"],
    "name": ["details4"]
    }

    This JSON structure is not valid because JSON keys must be unique within the same object.

    If your data is like this:

    [
    {"name": "details1"},
    {"name": "details2"},
    {"name": "details3"},
    {"name": "details4"}
    ]

    You can probably loop through each object and extract the name value as shown before.

    var jsonRes = {
    
  "name": ["details1", "details2", "details3", "details4"]
    
};

    
for (var i = 0; i < jsonRes.name.length; i++) {
    
  datagrid.addCell("name"); // Add the name or label (if it's the same for all)
    
  datagrid.addCell(jsonRes.name[i]); // Add the corresponding detail value
    
  datagrid.endRow();
    
}

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • SimonKing
    SimonKing Member

    Unfortunately that is not how my data is though :(

  • ArborRose
    ArborRose Coach

    That's too bad. And you can't go back to the source of the API code and talk to them about it?

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • SimonKing
    SimonKing Member

    Unfortunately not, its a global service used by 1000's of customers 'as is'.

  • ArborRose
    ArborRose Coach
    Answer ✓

    Can you reformat the JSON incoming, before you handle it?

    Loop through each object in the array, extracting the details from the "name" key and creating a new object with the key "details" associated with the value.

    // Simulating the incoming invalid JSON (you receive this from the API)
    
var invalidJson = [
    
  { "name": ["details1"] },
    
  { "name": ["details2"] },
    
  { "name": ["details3"] },
    
  { "name": ["details4"] }
    
];


    
// Initialize an array to store the reformatted data
    
var validJsonArray = [];


    
// Loop through each entry and reformat it
    
for (var i = 0; i < invalidJson.length; i++) {
    
  validJsonArray.push({
    
    "name": "name", // This assumes "name" is constant
    
    "details": invalidJson[i].name[0] // Extracting the detail from each object
    
  });
    
}


    
// The validJsonArray now contains the reformatted data
    
console.log(validJsonArray);

    After reformatting:
    [
    { "name": "name", "details": "details1" },
    { "name": "name", "details": "details2" },
    { "name": "name", "details": "details3" },
    { "name": "name", "details": "details4" }
    ]

    for (var i = 0; i < validJsonArray.length; i++) {
    
  datagrid.addCell(validJsonArray[i].name); // Adds "name" to the first column
    
  datagrid.addCell(validJsonArray[i].details); // Adds the corresponding details to the second column
    
  datagrid.endRow(); // End the current row
    
}

    If you regularly receive this malformed JSON from the API, you can wrap the reformatting code in a function and call it each time you receive the data.

    function reformatJsonData(invalidJson) {
    
  var validJsonArray = [];

    
  for (var i = 0; i < invalidJson.length; i++) {
    
    validJsonArray.push({
    
      "name": "name",
    
      "details": invalidJson[i].name[0]
    
    });
    
  }

    
  return validJsonArray;
    
}


    
// Usage
    
var reformattedData = reformatJsonData(invalidJson);

    
// Process the reformatted data as needed

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories