JSON Primer

I recently worked on a project, which includes a requirement to query a web service for customer transaction data. I had a choice of requesting either XML or JSON data from the server. I have worked with XML before, but I heard JSON is fast becoming the "preferred" format for web service communications, so I decided to learn about it quickly.

Intro

JSON stands for "JavaScript Object Notation." It is a text-based data interchange format much like XML. However, JSON is much simpler than XML with smaller grammar and represents the data structure more directly than XML. Although XML and JSON are somewhat similar, XML is better for representing documents while JSON is better for representing data.

Syntax

A JSON object is essentially a collection of key/value pairs. The object syntax begins with a left curly bracket ({) and ends with a right curly bracket (}). The key and value pairs are separated by a colon (:), and each set of key/value pair is separated by a comma (,). The values for each key can be an unordered or ordered list of values (array). The list of values starts with a left square bracket ([) and ends with a right square bracket (]). Each value within an array of values is separated by a comma (,). A value can represent any of the following:

• string
• number
• object
• array
• true
• false
• null

If the value is a string, it must be enclosed in double quotes ("). The key name is also enclosed in double quotes (").

Examples

Here are some examples of a JSON object syntax.

{"FirstName":"DB","LastName":"Guy","Website":"www.accessmvp.com/thedbguy"}

{"CustNo":"00138","ProductsPurchased":["Milk","Bread","Eggs","Butter"]}

{"Menu": [ {
    "Breakfast": [
        { "ItemName":"Eggs",
           "Price":2.75 },
        { "ItemName":"Hashbrown",
           "Price":3.50 }
    ] }, {
    "Lunch": [
        { "ItemName":"Burger",
           "Price":7.25 },
        { "ItemName":"Fries"
           "Price":4.50 }
    ] }
  ]
}

As you can see, white space is ignored when a JSON object is parsed. So, formatting a JSON object helps the developer read the code better. There are several JSON formatters available on the Internet.

To learn more about JSON, please visit the official JSON website at www.json.org.

For parsing JSON objects in VBA, I recommend using VBA-JSON at GitHub.