Fetch value from JSON

[Shishir Srivast]

JSON (What it is?), JavaScript Object Notation, is a lightweight readable format for structuring data. It’s a, language independent, text format and a collection of key/value pairs which can easily transmit the data between a server and web application.

I have learnt it’s simple and easy to extract the data from JSON when you understand the structure of it. So, sharing my thoughts and may be this could be useful for learners and other Community members to understand the basics and how it works.

Let’s start.. 😊 

Keys and Values are the main primary component of JSON. A key is always a string enclosed with quotes. A value can be a string, number, boolean expression, array, or object. A key/value pair, the key followed by a colon followed by the value. Key/value pairs are comma separated.

Example: “Key” : “Value”, 

Curly Brackets: { } represents an object of JSON string whereas Square Brackets : [ ] represents an array of values of JSON string. Example: 

Object

"Key1" : {                                      
  "Key2" : "Value2"
}

This is also an example of nested key/value. The key/value pair "Key2" : "Value2" is nested inside another key/value pair.

Array

"Key1" : {
  "Key2" : "Value2",
  "Key3" : [ "Value31", "Value32" ]
}

Key3 has values in an array format. The index of first value will start with zero (as per the array standards).

JSON.stringify() helps to convert an object or an array of JSON into string format before sending the data. Example

If you print below code in background script you will get result as [object Object], the same result can also be obtain if you do not provide the correct key name or wrong index pointer for an array which does not exist.

var jsonObj = { "key" : "value"};
gs.print(jsonObj); 

but, if you print after converting the JSON object into string using stringify(), you will be able to print the result as a string:

var jsonObj = { "key" : "value" };
gs.print(JSON.stringify(jsonObj));

Let’s see an example how to navigate to get the value of particular key by parsing the JSON but before that it’s always good to Format the JSON in pretty format (if required) for better user readability and that can be done by using JSON.stringify(variable name, undefined, 2), that gives JSON in structured format (as shown in below example). The structured formatted JSON provides an understanding how to dot-walk or use array indexes to extract the value from JSON. 

Example:

var jsonObject = { "key1": “value1”, "array1": [ { "key2": “value2” }, { "key3": “value3”}]};
JSON.stringify(jsonObject, undefined, 2);

 Result:

{
	" key1":” value1”,
		" array1": [
			{
				" key2": “value2”
			},
			{
				" key3": “value3”
			}
		]
}

Use Case A 

If there is only one value in each Columns and Rows for the table then simply just by doing dot-walking to reach attributes will give the result.

var getData = [];
var obj = {};
var payload = {
            "SearchResult": {
                        "tables": {
                                    "name": "PrimaryResult",
                                    "columns": {"name": "SID", "type": "string"},
                                    "rows": "abcdefgh-ijkl-mnop-qrst-123456789",
                        },
            },
};

obj[payload.SearchResult.tables.columns.name] = payload.SearchResult.tables.rows;        
getData.push(obj);
var finalResult = JSON.stringify(getData);
gs.print(finalResult);

Result:  

*** Script: [{"SID":"abcdefgh-ijkl-mnop-qrst-123456789"}]

Key Points:

1. To fetch value from the nested object, use the dot-walking followed by the key name.

To get the column named SID: payload.SearchResult.tables.columns.name
To get the row value: payload.SearchResult.tables.rows    

2. var obj = {}; obj[Key] = “value” : This syntax will create the Key name dynamically with result as {“Key” : “Value”}.
3. Use the JSON.parse() to convert the data in JavaScript object if payload variable is in string.

Use Case B 

If there are multiple values (in an Array) in each columns and rows for the table then simple just dot-walking to attribute will not give the result. Looping through array is required to fetch the value.

var payload, i, j, m;
var payload = {
	"SearchResult": {
		"tables": [
			{
				"name": "PrimaryResult",
				"columns": [
					{"name": "SID",
					 "type": "string"},
					{"name": "Machine",
					 "type": "string"},
					{"name": "Location",
					 "type": "string"},
				],
				"rows": [
					[
						"abcdefgh-ijkl-mnop-qrst-123456789",
						"SERVERSJ01",
						"San Jose",
					],
					[
						"abcdefgh-ijkl-mnop-qrst-987654321",
						"SERVERNY01",
						"New York",
					],
				]
			},
		]
	},
};

var getData = [];
for (i in payload.SearchResult.tables) { // loop through the tables to fetch the data from columns and rows
	for (j in payload.SearchResult.tables[i].rows) {
		var obj = {};
		for(m in payload.SearchResult.tables[i].columns){                      
			var columnName = payload.SearchResult.tables[i].columns[m].name.toString();      
			//Creates the key name in key/value pair dynamically.      
			obj[columnName] = payload.SearchResult.tables[i].rows[j][m].toString();    
		}
		getData.push(obj);
	}
}

var finalResult = JSON.stringify(getData, undefined, 2);
gs.print(finalResult);

Result:

*** Script: [
  {
    "SID": "abcdefgh-ijkl-mnop-qrst-123456789",
    "Machine": "SERVERSJ01",
    "Location": "San Jose"
  },
  {
    "SID": "abcdefgh-ijkl-mnop-qrst-987654321",
    "Machine": "SERVERNY01",
    "Location": "New York"
  }
]

Key Points:

1. To fetch the value from an array, loop through the objects.
2. If there are multiple arrays within array then loop through array with the corresponding indexes to fetch the value, if required.

obj[columnName] = payload.SearchResult.tables[i].rows[j][m].toString(); 

The variable name — payload.

Inside that, to access the members of tables use ["SearchResult.tables"].

Tables contains an array populated by objects, to access the object inside the array used [i], since here it has only 1 array so index 0 can also be used, if there are multiple array then loop through that.

Inside table array, to access the nested rows array’s values according to column index, used [j][m] which represents the particular rows value corresponding to respective column index. 

In Short:

Format the JSON in readable format (if needed).

Navigate to JSON object using dot-walking, if there are arrays in nested object then loop through the array to parse nested objects/array to fetch the values using respective indexes followed by the Key name.

Save the values into an object then push the object into an array. Use the JSON.stringify() method to convert object/array into string to send it further processing.

I will keep updating the content with different scenarios as and when I get more experience in this subject to share also as per your valuable feedback and suggestions. 

Thank you!