Intro

Understanding JSON is crucial for working with data today, especially when using APIs like SerpApi. Here is a short lesson on what JSON is and how to access data stored in this format.

We will go over the basics of what JSON is, what it's benefits are, and how to parse it and extract values in Javascript and Python.

We will also cover how to create a "selector" you can use with SerpApi's Google Sheets Extension.

A Note for SerpApi Google Sheets Extension Users

Skip this section if you are working with JSON using a programming language.

SerpApi offers a Google Sheets Extension as a no-code option for accessing the APIs. Using the extension requires very little knowledge of programming concepts. However, since results are returned in JSON,  you do need to have a basic understanding of how JSON works.

Google Sheets SERPAPI_RESULT formulas have two parts. The first part is the URL used to perform the search. The second part is the "selector" used to extract the specific data point you want to display in the cell.

In order to determine your selector, you need to understand how JSON is organized. You will use the same basic conventions used in programming languages to extract nested variables.

What is JSON?

JSON is a popular and convenient data format that is easily readable for both humans and machines.

The acronym stands for JavaScript Object Notation. While it was developed based on JavaScript objects, it exists independently of JavaScript, and most popular modern programming languages include code to generate and parse JSON.

From json.org:

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language Standard ECMA-262 3rd Edition - December 1999. JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. These properties make JSON an ideal data-interchange language.

Because JSON is lightweight, flexible, easy to read for both humans and machines, and supported by most programming languages, JSON is often considered the go-to format for data exchange between applications.

For these reasons, and because JSON represents structured data in a way that is more convenient for developers to work with than other formats, APIs like SerpApi usually return data in a JSON format.

How JSON is Organized

JSON is organized using two common structures:

Objects - an unordered collection of key/value pairs.  Most programming languages have a similar structure that may be referred to as objects, records, structs, dictionaries, hash tables, keyed lists, or associative arrays.

A JSON Object closely resembles a JavaScript Object. It is enclosed by a {  (left curly brace) and a } (right curly brace). Quotation marks wrap the keys. Keys and values are separated by a colon. Pairs are separated by a comma. For example: 

{
	"name": "Bob",
	"age": 54,
	"subscribed": true
}

Arrays - an ordered list of values. In other languages this might also be called a vector, list, or sequence. Arrays start with a [ (left bracket) and end with a ] (right bracket). Inside the bracket is a comma separated list of values, for example:

[
    "apple",
    "bannana",
    "pear",
    "kiwi"
]

Values in arrays, and in key/value pairs of objects have five forms they can take:

  1. String - a string of text wrapped in quotes, for example "Bill Gates".
  2. Number - a number, for example 2 or 2,356
  3. Boolean - either true or false.
  4. Null - empty or invalid value, null
  5. Nested Object or Array - Objects and Arrays can be nested inside each other.

The JSON response or document is always contained in an Object, and can have any number of Objects and Arrays nested inside.

Converting JSON to a Format You Can Work With

JSON typically needs to be converted into a native object supported by the programming language you are using. In Javascript, for example, you can use the JSON.parse() method to parse JSON into a Javascript Object. Python has a similar method, json.load, which converts JSON to a Python Dictionary.

When using most of SerpApi's libraries for specific languages, you can skip this step. The libraries contain methods to convert the JSON to a convenient workable format implicitly.

Extracting Values from JSON

The syntax depends on the programming language, but the basic approach is similar.

Individual values can be extracted using either the associated key, or the index of the array. When working with nested objects and arrays, we may need to string together many keys and indexes in order to get to the desired value. We always work from the outermost containing object and work our way inward.

Syntax

Before we look at an example, let's briefly go over how nested values are accessed in a few of the popular programming languages (and in a Google Sheets Extension "selector").

The general convention is to build a string of keys and indexes to specify a path from the outer-most container to the desired value. Depending on the language, keys and indexes can be separated by a dot or enclosed in square brackets.

Javascript - When working with JavaScript objects, either brackets or dots can be used to separate keys. Dots are usually considered more readable. Array indexes are always enclosed in brackets. Note that JavaScript is zero indexed - meaning we use [0] to get the first item in an array, and count up from there.

variable.key1.key2[0].key3[5];

Python - When working with Python Dictionaries, keys are enclosed in brackets and quotes. Array indexes are enclosed in brackets.

variable['key1']['key2'][0]['key3'][5];

Google Sheets Extension Selector -

When formulating a selector, we don't need to reference the containing object like we do in a programming language. That's because it's already implicit that we want to reference a nested value from the JSON response.

Just select the outer most key, and move inward until you reach the data point you need, separating each key or index with a dot:

key1.key2.0.key3.5

Extracting a Value

As an example, take a look at the JSON response for this SerpApi request to search Google for "Nintendo":

Let's say we want to extract the image URL from the first video game in the "Video games" section of the Knowledge Graph in the right sidebar.

Visit the link and scroll down to the video_games array in the JSON (hint: you can also click on the image in the HTML preview and the Playground will automatically select and scroll down to that data point for you).

You can see there are several Objects with the name, extensions, link, serpapi_link, and image keys inside an array. The array is part of a key value pair,  it's key is video_games.  If you scroll up a bit you can also see that video_games is inside another object, which has the key knowledge_graph:

The knowledge_graph key is inside a larger object, which includes the entire JSON response.

We are after the first image url in the list of video_games in the knowledge_graph. To extract or isolate this value, we need to start from the outside-most containing object and move inward.

Assuming the JSON response has been converted to a Javascript object and stored in a variable named results the JavaScript code to reference the Knowledge Graph is results.knowledge_graph. 

let knowledgeGraph = results.knowledge_graph;

To access the list of video games, we add video_games:

let videoGames = results.knowledge_graph.video_games;

To get the first item in the list of video games, we need to use an index to refer to the position in the list. We surround the index with square brackets and append it after video_games.

let firstVideoGame = results.knowledge_graph.video_games[0];
let secondVideoGame = results.knowledge_graph.video_games[1]

We want to get the image URL, which is associated with the image key inside the first item in the list of video games:

Javascript:

let firstVideoGameImage = results.knowledge_graph.video_games[0].image;

Python:

let firstVideoGameImage = results['knowledge_graph']['video_games'][0]['image'];

SerpApi Google Sheet Extension Selector:

knowledge_graph.video_games.0.image;

Conclusion

I hope you found this tutorial informative and easy to follow. If you have any questions, feel free to contact me at ryan@serpapi.com.