What Is JSON?

JSON is a data interchange format that is designed to be lightweight and easy to work with. It’s quite popular in web applications, and it can be considerably more flexible than a traditional relational data model. Several database engines support JSON, so you can store JSON data and use functions to operate on it. This includes decomposing, transforming, or even creating JSON data from regular relational data.

 

What Is JSON?

JSON is an acronym for JavaScript Object Notation. It was born as an XML alternative for message interchange between web application clients and servers. Let’s look at an example of the JSON format:

{ “ID”: 12987654, “fname”: “John”, “lname”: “Smith:, “Cities”: [“Paris”, “London”,”Quito”] }

 

The contents are fairly self-explanatory. The [ square brackets ] define a set of values – an array with the cities where he/she lived – for the cities element. One of the most important differences between the relational model and JSON is that: JSON doesn’t need to adhere to any schema. For example, we can have another JSON like:

 { “ID”: 12987655, “fname”: “Mary”, “lname”: “Fox:, “email”: [“[email protected], “[email protected]”] }

Using JSON in TeamSQL

Storing JSON Values

To store a JSON value in a table we need to use a JSON data type column. Let’s begin with an example that creates and populates a table (employee) with a JSON column (extra_info).

 

 

Extracting Element Values from JSON Data

Usually, we need to obtain an individual element from a JSON value; two essential operators do this: -> and ->>.

->> Extracts and returns the element content as simple text

-> Extracts and returns the original JSON type (which might be an object)

Let’s use the ->> operator to get the names of employees:

 

 

 

If we use the -> operator, we obtain the field “as-is” in the JSON format (not as plain text). For example:

 

 

 

Note that the value of the email is still in its original JSON format. In this particular case, the format is an array with two elements.

If we want to extract a single element from an array, we have two more operators: #> and #>>.

#>> Extracts and returns an array element as a simple text

#> Extracts and returns an array element as a JSON object

So if we want to obtain the second element from the emails array, we use the following SQL:

 

 

Populating Relational Data with JSON Elements

This is an exciting exercise. Suppose we want to add a column full_name to the employee table and populate it with the fname and lname values taken from the JSON. Let’s see how to do it by using the -> operator in the following image.

 

 

Closing

We used JSON data type in several SQL operations like SELECT, INSERT, extraction of elements and UPDATE. There are many other ways to use JSON; you can ever build a JSON from simple relational data. In a next article, we will continue working with JSON and TeamSQL. Let’s do it together!