The idea behind this article is to list as many examples of JSON nested elements as I can, an inclusion of sample data, relational data conversion to JSON data, JSON to relational data, conversion of JSON elements into separate columns and representing the same data in separate rows.

JSON stands for JavaScript Object Notation. JSON is the primary data representation for all NoSQL databases. This is a natural fit for developers, who use JSON as the data interchange format in their Applications. The relative ability of JSON (JSON records are well structured but easily extended) on its scalability has attracted the developers looking DB migrations in agile environments. Data and schema, in volume, can be hard to change. Rewriting a large dataset stored on the disk while keeping the associated Applications online can be time-consuming. It can take days of background processing, in moderate to large examples, to upgrade the data

Background

Most traditional relational database engines now support JSON. With SQL Server 2016, it’s easy to interchange JSON data between applications and the database engine. Microsoft has provided various functions and capabilities to parse JSON data. They tried to bring JSON data into relational storage. It also provides the ability to transform relational data into JSON and JSON data into denormalized data.

Having these additional JSON features built into SQL Server should make it easier for applications to exchange JSON data with SQL Server. This functionality provides the flexibility in the integration of JSON data into the relational database engine. Developers can write and invent complex queries during their periodic stages of the development process.

Relational databases refer to traditional data storage, the constructive and intuitive SQL language, complex query design, and ACID compliance. NoSQL offers different concepts – complex structures are placed together into the collections of the entities, where you can take everything you need with one read operation or where you can insert complex structures with a single write while following the CAP theorem.

Relational databases normalize the data to some degree; that is, rather than repeating a piece of data in multiple rows, a table that needs that information will store a foreign key, which points to another table that holds the data. On the other hand, this process means that the data is typically shredded from its original form to fit into tables and then reassembled at runtime by joining the tables in response to a query. This becomes particularly expensive as the data set grows, and the data needs to be partitioned among the multiple database servers.

JSON syntax is derived from JavaScript object notation syntax.

Data is in the name/value pairs. {“key””value”} – most common format for objects.

The JSON Values

In JSON, values must be one of the data types given below.

A string

A number

An object (JSON object)

An array

A boolean

Null

If you have parent/child (Fact/Dimension) relationships, where related child information is not changed frequently and you need to read the child records together with the parent without additional JOINS, you can store the child records in the parent table as a JSON array. In traditional databases, the normalization process ensures a minimized amount of information is duplicated, whereas in NoSQL, it's intentionally duplicate it to make it easier to use. Let’s say we want to represent the number of students taking a class. A normalized way of representing the data is given below. The use of an array denotes the dimension data of the relational table:

When you parse the JSON container, you will end up fetching the denormalized data in one table.

Let’s discuss the different dimensions of the sample data given below and represent the data in tabular and JSON file formats. Also, you will learn how to query a JSON file with the various available JSON constructs in SQL Server 2016,

Native JSON support in SQL Server 2016 provides you with a few functions to read and parse your JSON string into a relational format.

JSON_Value(): Scalar function returns a value from JSON on the specified path.

The sample output given below is an example of how to demonstrate the different dimensions of representing the data into JSON and the relational data. The example lists parent and child relationship and it is represented in JSON array (batter and topping) and nested objects as well.

Relational Data Presentation Using FOR JSON

The FOR JSON AUTO clause is similar to the FOR XML AUTO clause. It automatically formats the JSON output based on the column/tables hierarchy defined in the SQL query. The FOR JSON PATH clause is similar to the FOR XML PATH clause. It gives more control to define the structure using column alias with dot separators.

For example, let's create a sample table ‘EMP’ and ‘DEPT’ and insert few rows in it:

This option formats the JSON document automatically based upon the columns provided in the query. With the FOR JSON PATH option, the dot syntax is used for nested output.

SELECT E.EMPNO,E.ENAME,D.DEPTNO AS "DEPARTMENT.DEPTNO" ,D.DNAME AS "DEPARTMENT.DNAME",D.LOC AS "DEPARTMENT.LOCATION"
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
WHERE D.DEPTNO=10
FOR JSON PATH
SELECT E.EMPNO,E.ENAME,D.DEPTNO AS "DEPARTMENT.DEPTNO" ,D.DNAME AS "DEPARTMENT.DNAME",D.LOC AS "DEPARTMENT.LOCATION"
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
WHERE D.DEPTNO=10
FOR JSON PATH,ROOT('EMPLOYEE')

As we can see, the PATH option creates a wrapper class ‘department’ and nests properties deptno, dname, and location.

Let's transform the below sample data to JSON:

1

donut

Cake

Regular

None

1

donut

Cake

Regular

Glazed

1

donut

Cake

Regular

Sugar

1

donut

Cake

Regular

Powdered Sugar

1

donut

Cake

Regular

Chocolate with Sprinkles

1

donut

Cake

Regular

Chocolate

1

donut

Cake

Regular

Maple

1

donut

Cake

Chocolate

None

1

donut

Cake

Chocolate

Glazed

1

donut

Cake

Chocolate

Sugar

1

donut

Cake

Chocolate

Powdered Sugar

1

donut

Cake

Chocolate

Chocolate with Sprinkles

1

donut

Cake

Chocolate

Chocolate

1

donut

Cake

Chocolate

Maple

1

donut

Cake

Blueberry

None

1

donut

Cake

Blueberry

Glazed

1

donut

Cake

Blueberry

Sugar

1

donut

Cake

Blueberry

Powdered Sugar

1

donut

Cake

Blueberry

Chocolate with Sprinkles

1

donut

Cake

Blueberry

Chocolate

1

donut

Cake

Blueberry

Maple

1

donut

Cake

Devils Food

None

1

donut

Cake

Devils Food

Glazed

1

donut

Cake

Devils Food

Sugar

1

donut

Cake

Devils Food

Powdered Sugar

1

donut

Cake

Devils Food

Chocolate with Sprinkles

1

donut

Cake

Devils Food

Chocolate

1

donut

Cake

Devils Food

Maple

The below transformation holds nested objects. As we can see, there are a few more entries added for id 0002. In the above sample data, we can see there are four batter types and seven toppings to prepare 28 (1*4*7=28) different types of cake. Similarly, for id 0002, four batter types and thre toppings are used to prepare 12 (1*4*3) types of cake.

Transform JSON to Relational data

OPENJSON is a table-value function (TVF) that looks into JSON text, locates an array of JSON objects, iterates through the elements of the array and, for each element, returns one row in the output result. To read JSON from the file, load the file using the OPENROWSET construct into a variable. Sstocks.json is an example for demonstration. You can derive the path as per your requirements and the environment. In the following example, there's some SQL code that reads the content of the JSON file, using the OPENROWSET BULK function, and passes the content of JSON file (BulkColumn) to the OPENJSON function

A JSON file can be stored in local file system or globally (cloud storage).

SQL Server 2016 provides the functions for parsing and processing JSON text. The built-in JSON available in SQL Server 2016 are given below.

ISJSON( jsonText ) checks if the NVARCHAR text is properly formatted according to the JSON specification. You can use this function to create check constraints on NVARCHAR columns, which contains\ JSON text.

‘$.info. “first name”‘ – references “first name” property in info object. If the key contains some special characters such as space, dollar, etc., it should be surrounded by double quotes.

The dollar sign ($) represents the input JSON object (similar to root “/” in XPath language). You can add any JavaScript-like property or an array after “$” to reference properties in JSON objects. One simple example of a query, where these built-in functions are used is given below.

How to Define Nested Objects in JSON

In the employee example given below, the employeeDepartment is the root of the JSON. The array element DEPT has dimension data, which represents the department details of each employee. The employee JSON structure has three objects.

Conclusion

SQL 2016 contains some very powerful JSON constructs. Mixing the power of relational databases with the flexibility of JSON offers many benefits for migration, integration, and deployment. It is flexible because of the simple syntax and the relatively little overhead needed to maintain and manage the JSON data. The powerful JSON SQL constructs enable us to query and analyze JSON data as well as transform JSON to relational data and relational data to JSON.

There are plenty of examples and resources available under various links. This is an effort to combine real-world scenarios and details the various ways of JSON data manipulation, using SQL 2016 JSON constructs. NoSQL offers different concepts — complex structures are placed together into the collections of the entities, where you can take everything you need with one read operation or where you can insert complex structures with a single write. The bad side is that sometimes you want to organize your information in different collections, and then you will find that it is very hard to JOIN entities from the two collections.

With newest version of SQL Server, you have options to choose between these two concepts and use the best of both worlds. In your data models, you can choose when to use traditional relational structures and when to introduce NoSQL concepts.