JSON stands for JavaScript Object Notation. After writing this previous post on JSON, I’ve been receiving a lot of queries about how to parse different JSON structures and how to create JSON. So I’m writing this post to make JSON understandable for everyone. Let’s get started!
Update: This article is still relevant in 2020
What is JSON?
JSON is a data exchange format. Let’s make it simple – JSON is a format in which data is sent or received so that it’s easy to read the data.
Can you give me one more example of data exchange format?
XML is another example of data exchange format. XML was widely used before JSON gained popularity. XML is used even today but JSON kicks XML’s ass any day 😀
Show me a practical example that uses JSON?
JSON is used everywhere. One simple example – Youtube API. If I want to pull trending videos in US then I can call youtube API which gives response in JSON format which looks like image below.
Explore a wide list of JSON APIs here – Public JSON API list
Understanding JSON (Read this clearly)
Now that you know how JSON looks and its practical use cases, Let’s try to understand the structure of JSON.
JSON is built with just 2 data types – Object/Dictionary
and Array
.
Any valid JSON is always a Key-value pair object (dictionary) or an Array or a combination of both.
The one line just above this is all you need to master JSON. Most of them fail to understand this in the beginning.
Object
An object is a set of key-value pairs. An object begins with { and ends with } . Key-value pairs are separated by a comma.
Object is realized as different data types in different languages.
- Dictionary in Python, VBA, VB.Net etc.
- Associative array in PHP
- Object in Javascript
- Hash tables, key-value pairs etc
Its basically a collection of keys and values. Here’s an example below.
{ name: "Coding is Love", url: "https://codingislove.com" }
An Object’s keys should always be a string in JSON. So it should be wrapped in quotes. Although regular object’s keys need not be wrapped in strings as shown in example above. So Object in JSON looks like this –
{ "name": "Coding is Love", "url": "https://codingislove.com" }
An Object’s values are usually accessed by specifying its property/keys’s name. Example in Javascript :
var siteData = { name: "Coding is Love", url : "https://codingislove.com" } console.log(siteData.name) //logs "Coding is Love" console.log(siteData.url) //logs "https://codingislove.com" console.log(siteData["name"]) //logs "Coding is Love"
It is accessed the same way in most programming languages. The syntax might change a bit but the basic concept remains the same. Access Values using it’s Key.
Note: Dictionaries in VBA can be accessed using siteData("name")
Array
An Array is a collection of values separated by comma. It starts with [ and ends with ]
Example :
["Name 1","Name 2","Name 3","Name 4"]
Array values are accessed by specifying Index. Array index start with 0.
var namesList = ["Name 1","Name 2","Name 3","Name 4"]; console.log(namesList[0]) //Logs "Name 1" console.log(namesList[3]) //Logs "Name 4"
Note: If you are using VBA-JSON in Excel VBA then it converts arrays into collections whose index start with 1 and can be accessed like this – namesList(1)
JSON Examples
If you understand Objects and arrays then JSON is very simple. Let’s see few possible ways in which JSON can be built.
JSON with Object
{ "name": "Coding is Love", "url": "https://codingislove.com", "email": "[email protected]", "job": "Writing awesome content", "phone": 1234567890 }
JSON with Array
[1,2,3,4,5,6,7]
JSON with Array of objects
[ { "userId": 1, "id": 1, "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit", "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto" }, { "userId": 1, "id": 2, "title": "qui est esse", "body": "est rerum tempore vitae\nsequi sint nihil reprehenderit dolor beatae ea dolores neque\nfugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis\nqui aperiam non debitis possimus qui neque nisi nulla" } ]
Object inside an object
{ "id": 1, "name": "Leanne Graham", "username": "Bret", "email": "[email protected]", "address": { "street": "Kulas Light", "suite": "Apt. 556", "city": "Gwenborough", "zipcode": "92998-3874", "geo": { "lat": "-37.3159", "lng": "81.1496" } } }
Array inside object
{ "id": 1, "profile": { "name": "John Doe", "Photos": ["url1", "url2"] } }
So, Basically we can dump objects, arrays inside each other in any way we want and build a valid JSON as long as we follow the rules of Array and Object.
Parse JSON in different Languages
Few languages have built-in support for JSON and few don’t. Let’s see how to Build JSON and parse JSON in few languages.
Javascript
Javascript has built-in methods :
- JSON.Stringify(JSON Object) – Converts JSON Object to string. Usually used when sending data to server.
var mydata = {name:"Coding is Love",score:25}; var JsonString = JSON.Stringify(mydata); //post JsonString to server
- JSON.Parse(JSON String) – Converts JSON string into object which can be used to access the data. It is usually used when data is received from external source and parse data in it.
var mydata = get JSON from your external source //lets say we are pulling the data that we posted from previous example. parsedObject = JSON.parse(mydata) alert(parsedObject.score) //alerts 25
Similar methods to parse and stringify JSON exist in most languages.
Python
Python has built-in JSON Encoder and Decoder. Documentation here – https://docs.python.org/2/library/json.html
import json json.loads(JSON String) json.dumps(Dictionary)
PHP
PHP also has built-in methods for parsing JSON. Documentation here – http://php.net/manual/en/book.json.php
json_decode(JSON string) json_encode(Associative array)
VBA
There’s an in-detail post here – Parse JSON in Excel
Ruby
There’s a ruby gem here – https://github.com/flori/json
require 'json' JSON.parse(string) JSON.generate(object)
Wrapping up
If you read this post completely then I’m sure you’ve understood JSON! Now, Try using it in real life.
Need some learning project? Let’s say you want to build a custom website which shows only a particular set of youtube videos. Hit the youtube API, parse the JSON and become a JSON master!
If you have any questions or feedback, comment below.
- Flutter lookup failed in @fields error (solved) - July 14, 2023
- Free open source alternative to Notion along with AI - July 13, 2023
- Threads API for developers for programmatic access - July 12, 2023
Dear Ranjith,
I am very impressed about your post ever before.
Can you help me to convert PHP language to Visual Basic about code bellow
Thank you for your attention and help.
https://bin.codingislove.com/ukunozarax.php
Hi, Did you mean VB.Net or VBA?
Dear Ranjith
I mean VBA
Thank you
Here’s the code – https://bin.codingislove.com/ifuzilojar.vbs
Make sure to read about post request and basic authentication here – https://codingislove.com/http-requests-excel-vba/ and post your further queries on forum. Hope that helps!
Ranjith – great post thanks. I’m trying to import and parse this API into excel using your method but getting stuck. Can you help:
https://api.lloydsbank.com/open-banking/v1.2/branches
Adam
Hi Adam, This should work – https://bin.codingislove.com/usarapukiv.vbs
Thanks – that’s great. One quick follow up – for me to then parse the info contained in for example “BranchOtherMediatedServices” what do I need to amend? If I follow the code above it gets to a “Application-defined or object-defined error”.
Really appreciate your help
You’re getting an error because It is an array. You have to access items inside it using an index. This should work – https://bin.codingislove.com/xohifoyudu
thank you for your share, awesome work (i needed help for vba)
Hi James, Glad it helped 🙂
hi thank you for this, my question is can we convert Excel charts to json format? i.e to save it as template file but in json format
Hi Ali, You can write a custom script to convert excel charts into Json format. Get started here – https://codingislove.com/excel-json/
Hi Ranjith, thank you for your kind reply I think my question was not in the correct way apologize for that , the fact I wanted to import chart similar to excel charts to power bi thought json might work but I don’t know charts on power bi are kind of not nice so thought to have example pie chart visual same as excel ones to PBI if can give sample that will be great
Sorry, I’m not an expert with Power BI and charts. I can only help if you have already written some code.
Thanks Ranjith
Hi Ranjith, My question is i want to extract the one id column from the json string and then add the one link to it like this one, and i want to paste it into excel sheet (XSSFWORKBOOK),please help me on the how to do?
When we click on this link it will generate the receiptImage of customers , i have an json string in the database table from there json string i want to extract the id column.
Can you share a sample of the JSON that you have?