How do I parse a JSON file with nested objects using JS?

Question

I am very new to JavaScript and have been given the task of converting various JSON files into excel spreadsheets. I am having trouble understanding how to parse a JSON file that has nested objects or nested arrays. Please forgive me if I am not using the right wording. The following is a sample of the JSON data I need to parse.

  [{
        "name": "Kindergarten",
        "subject": "Math",
        "framework": "NC:  Standard Course of Study",
        "standards": [
            {
                "id": 1306687,
                "name": "NC.K.CC.1",
                "short_description": "Standard 1."
            },
            {
                "id": 1306688,
                "name": "NC.K.CC.1.a",
                "short_description": "Standard 2."
            },
            {
                "id": 1306689,
                "name": "NC.K.CC.1.b",
                "short_description": "Standard 3."
            }
           
        ]
    }]

I have tried so many different loops and can only seem to parse the first part of the file and not the nested part. I basically need the data to look like this when it's done:

    name    subject framework   standards/0/id  standards/0/name    standards/0/short_description
    Kindergarten    Math    NC:  Standard Course of Study   1306687 NC.K.CC.1   Standard 1.
    Kindergarten    Math    NC:  Standard Course of Study   1306688 NC.K.CC.1.a Standard 2.
    Kindergarten    Math    NC:  Standard Course of Study   1306689 NC.K.CC.1.b Standard 3.

Any guidance you can give will be immensely helpful.

Answer 1

I believe the issue is you are trying to find a coding solution with js and excel only, which I don't believe will work as the js excel libraries I'm familiar with only work with json, not nested json. This code will work and run, but it only takes the first level of json, not the nested one, which is the second level. I used npm to install the XLSX module with: npm install xlsx in terminal.

const XLSX=require('xlsx')

const jsonData= [{
    "name": "Kindergarten",
    "subject": "Math",
    "framework": "NC:  Standard Course of Study",
    "standards": [
        {
            "id": 1306687,
            "name": "NC.K.CC.1",
            "short_description": "Standard 1."
        },
        {
            "id": 1306688,
            "name": "NC.K.CC.1.a",
            "short_description": "Standard 2."
        },
        {
            "id": 1306689,
            "name": "NC.K.CC.1.b",
            "short_description": "Standard 3."
        }
       
    ]
}]

const convertJsonToExcel=()=>{
    const workSheet = XLSX.utils.json_to_sheet(jsonData);
    const workBook = XLSX.utils.book_new();

    XLSX.utils.book_append_sheet(workBook, workSheet, "jsonData");
    XLSX.write(workBook, {bookType: "xlsx", type:"buffer"})
    XLSX.writeFile(workBook, "jsonData.xlsx")
}
convertJsonToExcel()

Here is the result:

So my point is you may not have a solution, unless you add in another language library like python or flatten out the json file BEFORE you try to export it to Excel using code.

This is what I found on a similar question on Code Project Export Nested Json to Excel:

This is data that can not be displayed on an Excel sheet as Excel can only display simple table, where you have a multilevel data structure...

To summarize, I think you are looking for the wrong solution if you try to use just JavaScript by itself.

Answer 2

You have a document:

let doc= '[{
    "name": "Kindergarten",
    "subject": "Math",
    "framework": "NC:  Standard Course of Study",
    "standards": 
    [
        {
            "id": 1306687,
            "name": "NC.K.CC.1",
            "short_description": "Standard 1."
        },
        {
            "id": 1306688,
            "name": "NC.K.CC.1.a",
            "short_description": "Standard 2."
        },
        {
            "id": 1306689,
            "name": "NC.K.CC.1.b",
            "short_description": "Standard 3."
        }           
    ]
}]'

Parse to a json object with JSON.parse(doc) Then you can iterate over the JSON array with "Nested For Loops":

let data = JSON.parse(doc);
for (let i =0; i < data.length; i++)
{
    for(let j = 0; j < data[i].standards.length; j++)
    {
        // obviously you aren't logging to console but for purposes of the demonstration I will
        console.log(`${data[i].name},${data[i].subject},${data[i].framework},${data[i].standards[j].id},${data[i].standards[j].name},${data[i].standards[j].short_description}`);
    }
}

Answer 3

Simply use JSON.parse("<your JSON string>") for parsing. This function returns an object tree reflecting the processed JSON. Then you can access all object properties in your loop and generate a CSV file, which can be opened with Excel.

let csv = "";

const objects = JSON.parse("<your JSON string>");
for (const o of objects) {
  for (const std of o.standards) {
    csv += `${o.name},${o.subject},${o.framework},${std.id},${std.name},${std.short_description}`;
  }
}

Working example:

const jsonData = `[{
  "name": "Kindergarten",
  "subject": "Math",
  "framework": "NC:  Standard Course of Study",
  "standards": [{
      "id": 1306687,
      "name": "NC.K.CC.1",
      "short_description": "Standard 1."
    },
    {
      "id": 1306688,
      "name": "NC.K.CC.1.a",
      "short_description": "Standard 2."
    },
    {
      "id": 1306689,
      "name": "NC.K.CC.1.b",
      "short_description": "Standard 3."
    }
  ]
}]`;

/* Escapes all quotes in the input in order to not break the CSV */
function val(input) {
  return input?.replaceAll ? input.replaceAll('"', '""') : input;
}

let csv = "";

const objects = JSON.parse(jsonData);
for (const o of objects) {
  for (const std of o.standards) {
    csv += `"${val(o.name)}","${val(o.subject)}","${val(o.framework)}","${val(std.id)}","${val(std.name)}","${val(std.short_description)}"`;
  }
}

console.log(csv);

EDIT: Modified the source code to generate more save CSV (now double-quotes and commas are allowed in the input). Thanks to @phuzi!

Answer 4

try this;

var data = [{
    "name": "Kindergarten",
    "subject": "Math",
    "framework": "NC:  Standard Course of Study",
    "standards": [
        {
            "id": 1306687,
            "name": "NC.K.CC.1",
            "short_description": "Standard 1."
        },
        {
            "id": 1306688,
            "name": "NC.K.CC.1.a",
            "short_description": "Standard 2."
        },
        {
            "id": 1306689,
            "name": "NC.K.CC.1.b",
            "short_description": "Standard 3."
        }
       
    ]
}]

const custom = [];
data.forEach((element)=>{
  const {name, subject,framework} = element;
  element.standards.forEach((elementInner)=>{
  const {id , name : stdName,short_description} = elementInner;
    custom.push({name, subject,framework, id, stdName, short_description});
  });
});
console.log(custom);