Unnest JSON Data

Ever thought of displaying nested JSON data inside Tablegrid. Well, this tutorial is definitely for you as we discuss different ways of rendering data inside the Tablegrid.

This use case covers:

  • Unnesting JSON data into Multiple Rows (Repeat)
  • Unnesting JSON data into Multiple Columns
  • Unnesting JSON data into Columns (Field values as column names)

We are using the same data for all the use cases that we are about to discuss. This data is stored on a MongoDB collection and using the connectors option from the Bind Data section it is loaded onto the respective controls.

JSON Data (Raw Response)

{
  "error": null,
  "result": {
    "rows": [
      {
        "_id": "620e50a0608c6e9831d2241e",
        "demographic": {
          "rollNo": "137",
          "name": "Thomas Wayne"
        },
        "subject": [
          {
            "name": "Applied Mathematics",
            "score": 90,
            "maximumMarks": 100
          },
          {
            "name": "Advance Java",
            "score": 80,
            "maximumMarks": 100
          },
          {
            "name": "Blockchain",
            "score": 70,
            "maximumMarks": 100
          }
        ]
      },
      {
        "_id": "620e50a0608c6e9831d2241f",
        "demographic": {
          "rollNo": "47",
          "name": "Clark Kent"
        },
        "subject": [
          {
            "name": "Applied Mathematics",
            "score": 85,
            "maximumMarks": 100
          },
          {
            "name": "Advance Java",
            "score": 92,
            "maximumMarks": 100
          },
          {
            "name": "Blockchain",
            "score": 77,
            "maximumMarks": 100
          }
        ]
      },
      {
        "_id": "620e50a0608c6e9831d22420",
        "demographic": {
          "rollNo": "96",
          "name": "Barry Allen"
        },
        "subject": [
          {
            "name": "Applied Mathematics",
            "score": 70,
            "maximumMarks": 100
          },
          {
            "name": "Advance Java",
            "score": 99,
            "maximumMarks": 100
          },
          {
            "name": "Blockchain",
            "score": 89,
            "maximumMarks": 100
          }
        ]
      }
    ]
  }
}    

 
Before Unnesting JSON Data

Unnesting JSON data into Multiple Rows

In this use case, the data is directly binded onto the tablegrid using connectors option. In the connectors option there is a transform response section which can transform/convert our data/response before displaying it onto the tablegrid.
Use the following JS Code to unnest the data into multiple rows.

Code

output = [];
data.result.rows.map(records => {
    records.subject.map(subject => {
        let row = {};
        row.rollNo = records.demographic.rollNo;
        row.name = records.demographic.name;
        row.subject = subject.name;
        row.scored = subject.score;
        row.maximumMarks = subject.maximumMarks;
        output.push(row);
    });
});
data = output;

 
JSON Data ( Transformed Response)

[
  {
    "rollNo": "137",
    "name": "Thomas Wayne",
    "subject": "Applied Mathematics",
    "scored": 90,
    "maximumMarks": 100
  },
  {
    "rollNo": "137",
    "name": "Thomas Wayne",
    "subject": "Advance Java",
    "scored": 80,
    "maximumMarks": 100
  },
  {
    "rollNo": "137",
    "name": "Thomas Wayne",
    "subject": "Blockchain",
    "scored": 70,
    "maximumMarks": 100
  },
  {
    "rollNo": "47",
    "name": "Clark Kent",
    "subject": "Applied Mathematics",
    "scored": 85,
    "maximumMarks": 100
  },
  {
    "rollNo": "47",
    "name": "Clark Kent",
    "subject": "Advance Java",
    "scored": 92,
    "maximumMarks": 100
  },
  {
    "rollNo": "47",
    "name": "Clark Kent",
    "subject": "Blockchain",
    "scored": 77,
    "maximumMarks": 100
  },
  {
    "rollNo": "96",
    "name": "Barry Allen",
    "subject": "Applied Mathematics",
    "scored": 70,
    "maximumMarks": 100
  },
  {
    "rollNo": "96",
    "name": "Barry Allen",
    "subject": "Advance Java",
    "scored": 99,
    "maximumMarks": 100
  },
  {
    "rollNo": "96",
    "name": "Barry Allen",
    "subject": "Blockchain",
    "scored": 89,
    "maximumMarks": 100
  }
]

 
Unnested JSON Data

Unnesting JSON data into Multiple Columns

In the previous multiple rows use case, the data after transformation appears to be quite redundant which can be optimized by unnesting the original JSON data into repeat column format. The data binding i.e. the connector configuration for the tablegrid remains the same as the previous one.
Use the following JS Code to unnest the data into multiple columns. This code makes the repeating column name unique by appending an incrementing integer to it.

Code

output = [];
let counter;
data.result.rows.map(records => {
  let row = {};  
  counter = 1;
  records.subject.map(subject => {
    row.rollNo = records.demographic.rollNo;
    row.name = records.demographic.name;
    row[`subject${counter}`] = subject.name;
    row[`scored${counter}`] = subject.score;
    row[`maximumMarks${counter}`] = subject.maximumMarks;
    counter++;
  });
  output.push(row);
});
data = output;

 
JSON Data ( Transformed Response)

[
  {
    "rollNo": "137",
    "name": "Thomas Wayne",
    "subject1": "Applied Mathematics",
    "scored1": 90,
    "maximumMarks1": 100,
    "subject2": "Advance Java",
    "scored2": 80,
    "maximumMarks2": 100,
    "subject3": "Blockchain",
    "scored3": 70,
    "maximumMarks3": 100
  },
  {
    "rollNo": "47",
    "name": "Clark Kent",
    "subject1": "Applied Mathematics",
    "scored1": 85,
    "maximumMarks1": 100,
    "subject2": "Advance Java",
    "scored2": 92,
    "maximumMarks2": 100,
    "subject3": "Blockchain",
    "scored3": 77,
    "maximumMarks3": 100
  },
  {
    "rollNo": "96",
    "name": "Barry Allen",
    "subject1": "Applied Mathematics",
    "scored1": 70,
    "maximumMarks1": 100,
    "subject2": "Advance Java",
    "scored2": 99,
    "maximumMarks2": 100,
    "subject3": "Blockchain",
    "scored3": 89,
    "maximumMarks3": 100
  }
]

 
Unnested JSON Data

Unnesting JSON data into Columns (Field values as column names)

This use case intends to improve the previous Multiple columns use case by eliminating repetition of columns and using field names as a substitute to column names. If you have observed that in the previous use case, the subject name, scored marks and maximum marks fields are repeated for each record of subject array, so to optimize this, the subject name can be taken as the column name and scored marks and maximum marks can be put together inside this column. Use the following JS Code to unnest the data into multiple columns.

Code

output = [];
data.result.rows.map(records => {
  let row = {};
  records.subject.map(subject => {
    row.rollNo = records.demographic.rollNo;
    row.name = records.demographic.name;
    row[subject.name] = `${subject.score}/${subject.maximumMarks}`;
  });
  output.push(row);
});
data = output;

 
JSON Data (Transformed Response)

[
  {
    "rollNo": "137",
    "name": "Thomas Wayne",
    "Applied Mathematics": "90/100",
    "Advance Java": "80/100",
    "Blockchain": "70/100"
  },
  {
    "rollNo": "47",
    "name": "Clark Kent",
    "Applied Mathematics": "85/100",
    "Advance Java": "92/100",
    "Blockchain": "77/100"
  },
  {
    "rollNo": "96",
    "name": "Barry Allen",
    "Applied Mathematics": "70/100",
    "Advance Java": "99/100",
    "Blockchain": "89/100"
  }
]

 
Unnested JSON Data