The JSON In SQL 2016

Abstract

This blog helps to explain the Application of JSON data and use new SQL 2016 constructs that enables an integration of JSON data to a relational schema. The idea behind this article is to list as many examples of JSON nested elements, sample data to be converted to JSON, JSON to the relational data, JSON elements to a separate column and JSON data to the separate rows.

Introduction

JSON stands for JavaScript Object Notation. JSON is the primary data representation for all NoSQL databases.This is a natural fit for the 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 of the traditional relational database engine now supports JSON. With SQL Server 2016, It's easy to interchange JSON data between the Applications and database engine. Microsoft has provided various functions and capabilities to parse JSON data. They tried to bring JSON data into a relational storage. It also provides an ability to transform the relational data into JSON and JSON data into the denormalized data.

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

Relational databases refers to the traditional data storage, Constructive and Intuitive SQL language, Complex query design and ACID property. NoSQL offers different concepts - complex structures are placed together into the collections of the entities, where you can take everything, which you need with one read operation or where you can insert complex structure with a single write and follows CAP property.

The 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 reassemble at the run time by joining the tables in response to a query. This becomes particularly expensive as the data set grows and the data need to be partitioned among the multiple database servers.

JSON Syntax Rules

JSON syntax is derived from JavaScript object notation syntax.

• Data is in the name/value pairs.
  {"key""value"} – most common format for objects
  
  
• Data is separated by commas.
  {"key""value"},{"key""value"}
  
  
• Curly braces holds the objects.
  {"key"{"key""value"}}
  
  
• Square brackets holds the arrays.
  {"key"[ {"key""value"},{"key""value"} ]}

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

Basic Structure

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 the additional JOINS, you can store the child records in the parent table as JSON array.

In the traditional database, normalization process ensures to minimize the amount of information that duplicates but whereas in NoSQL, intentionally duplicate it to make it easier to use. Let's say, representing a 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

{
    course "Basic programming",
        room "1A",
        students[{
            id 1,
            name "Prashanth"
        }, {
            id 2,
            name "Jayaram"
        }]
}

Here's a denormalized data.

[{
    course "Basic programming",
    room "1A",
    studentId 1,
    studentName "Prashanth"
}, {
    course "Basic programming",
    room "1A",
    studentId 2,
    studentName "Jayaram"
}]

When you parse the JSON container, you will end up in 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 format. Also, you will learn to know how to query JSON file with the various available JSON constructs in SQL 2016

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

1. OPENJSON() - Table valued function parses JSON text and returns row set view of JSON.
2. 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 dimension of representing the data into a 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. 





Transform sample data given above to JSON

[{
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters": {
        "batter": [{
            "id1": "1001",
            "type1": "Regular"
        }, {
            "id1": "1002",
            "type1": "Chocolate"
        }, {
            "id1": "1003",
            "type1": "Blueberry"
        }, {
            "id1": "1004",
            "type1": "Devils Food"
        }]
    },
    "topping": [{
        "id2": "5001",
        "type2": "None"
    }, {
        "id2": "5002",
        "type2": "Glazed"
    }, {
        "id2": "5005",
        "type2": "Sugar"
    }, {
        "id2": "5007",
        "type2": "Powdered Sugar"
    }, {
        "id2": "5006",
        "type2": "Chocolate with Sprinkles"
    }, {
        "id2": "5003",
        "type2": "Chocolate"
    }, {
        "id2": "5004",
        "type2": "Maple"
    }]
}, {
    "id": "0002",
    "type": "donut",
    "name": "cup Cake",
    "ppu": 0.5,
    "batters": {
        "batter": [{
            "id1": "1001",
            "type1": "Regular"
        }, {
            "id1": "1002",
            "type1": "Chocolate"
        }, {
            "id1": "1003",
            "type1": "Blueberry"
        }, {
            "id1": "1004",
            "type1": "Devil's Food"
        }]
    },
    "topping": [{
        "id2": "5001",
        "type2": "None"
    }, {
        "id2": "5002",
        "type2": "Glazed"
    }, {
        "id2": "5005",
        "type2": "Sugar"
    }]
}]

Transform JSON to Relational data

OPENJSON is a table-value function (TVF). which 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 OPENROWSET construct into a variable. The stocks.json is an example for the demonstration. You can derive the path as per your requirement and the environment.

In the following example is shown SQL code, which reads the content of the JSON file, using OPENROWSET BULK function and passes the content of JSON file (BulkColumn) to OPENJSON function

JSON file can be stored in local file system or global (Cloud storage).

SELECT ID, type, name, ppu, type1 batter, type2 topping FROM
OPENROWSET(BULK N '\\hq6021\c$\stocks.json', SINGLE_CLOB) AS json
CROSS APPLY OPENJSON(BulkColumn)
WITH(id nvarchar(40), type nvarchar(40), name NVARCHAR(MAX), ppu NVARCHAR(MAX), batters NVARCHAR(MAX) AS JSON, topping NVARCHAR(MAX) AS JSON) AS t
CROSS APPLY
OPENJSON(batters, '$.batter')
WITH(id1 nvarchar(100), type1 nvarchar(20))
CROSS APPLY
OPENJSON(topping)
WITH(id2 nvarchar(100), type2 nvarchar(20))

Built-in functions for JSON processing

SQL Server 2016 provides the functions for parsing and processing JSON text. JSON built-in functions, which are 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
  
• JSON_VALUE( jsonText, path ) parses jsonText and extracts the scalar values on the specified JavaScript-like path (see below for some JSON path examples).
  
• JSON_QUERY( jsonText, path ) that parses jsonText and extracts objects or arrays on the specified JavaScript-like path (see below for some JSON path examples)
  

These functions use JSON paths for referencing the values or objects in JSON text. JSON paths use JavaScript-like syntax for referencing the properties in JSON text. Some examples are given below.

• '$' – references entire JSON object in the input text.
  
• '$.property1' – references property1 in JSON object.
  
• '$[4]' – references 5-th element in JSON array (indexes are counted from 0 like in JavaScript).
  
• '$.property1.property2.array1[5].property3.array2[15].property4' – references complex nested property in the JSON object.
  
• '$.info. "first name"' – references "first name" property in info object. If key contains some special characters such as space, dollar, etc., it should be surrounded with double quotes.

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 object. One simple example of a query, where these built-in functions are used are given below.

DECLARE @MyJSON NVARCHAR(4000) = N '{
"info" {
    "type"
    1, "address" {
        "town"
        "Louisville", "county"
        "Boulder", "country"
        "USA"
    }, "tags" ["Snow", "Breweries"]
}, "LocationType"
"East", "WeatherType"
"Cold"
}
'
Select * from OPENJSON(@MyJSON)
WITH(type int '$.info.type', LocationType varchar(20)
    '$.LocationType', WeatherType varchar(20)
    '$.WeatherType', town varchar(200)
    '$.info.address.town', county varchar(200)
    '$.info.address.county', country varchar(200)
    '$.info.address.country') AS t
CROSS APPLY
OPENJSON(@MyJSON, '$.info.tags')

How to define Nested Objects in JSON

The examples given above also contains a sample data that represents nested object.

DECLARE @json NVARCHAR(1000)
SELECT @json = N '{
"Employee" [{
    "Element"
    1
}, {
    "Element"
    2
}, {
    "Element"
    "n"
}]
}
'

Parsing Nested elements of JSON

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

DECLARE @MyJSON NVARCHAR(4000) = N '{
"EmployeeDepartment": [{
    "EmployeeID": "E0001",
    "FirstName": "Prashanth",
    "LastName": "Jayaram",
    "DOB": "1983-02-03",
    "DEPT": [{
        "EmployeeDepartment": "Ducks"
    }, {
        "EmployeeDepartment": "Blues"
    }]
}, {
    "EmployeeID": "E0002",
    "FirstName": "Prarthana",
    "LastName": "Prashanth",
    "DOB": "2015-07-06",
    "DEPT": [{
        "EmployeeDepartment": "Red Wings"
    }]
}, {
    "EmployeeID": "E0003",
    "FirstName": "Pravitha",
    "LastName": "Prashanth",
    "DOB": "2015-07-06",
    "DEPT": [{
        "EmployeeDepartment": "Red Wings"
    }, {
        "EmployeeDepartment": "Green Bird"
    }]
}]
}
'
--SELECT * FROM OPENJSON(@MyJSON)
SELECT
EmployeeID,
FirstName,
LastName,
DOB,
DEPT,
EmployeeDepartment
FROM OPENJSON(@MyJSON, '$.EmployeeDepartment')
WITH(EmployeeID varchar(10), FirstName varchar(25), LastName varchar(25), DOB varchar(25), DEPT NVARCHAR(MAX) AS JSON) AS E
CROSS APPLY
OPENJSON(DEPT)
WITH(EmployeeDepartment nvarchar(100))

Reading JSON into separate rows

How about pulling them in separate rows, using JSON_Value() with OPENJSON() function. The query given below gives an overview of applying the JSON constructs on the nested elements.

DECLARE @MyJSON NVARCHAR(4000) = N '{
"EmployeeDepartment": [{
    "EmployeeID": "E0001",
    "FirstName": "Prashanth",
    "LastName": "Jayaram",
    "DOB": "1983-02-03",
    "DEPT": [{
        "DeptID": "D1",
        "DName": "Ducks"
    }, {
        "DeptID": "D2",
        "DName": "Blues"
    }]
}, {
    "EmployeeID": "E0002",
    "FirstName": "Prarthana",
    "LastName": "Prashanth",
    "DOB": "2015-07-06",
    "DEPT": [{
        "DeptID": "D3",
        "DName": "Red Wings"
    }]
}, {
    "EmployeeID": "E0003",
    "FirstName": "Pravitha",
    "LastName": "Prashanth",
    "DOB": "2015-07-06",
    "DEPT": [{
        "DeptID": "D3",
        "DName": "Red Wings"
    }, {
        "DeptID": "D4",
        "DName": "Green Bird"
    }]
}]
}
'
SELECT
JSON_Value(c.value, '$.EmployeeID') as EmployeeID,
    JSON_Value(c.value, '$.FirstName') as FirstName,
    JSON_Value(C.value, '$.DOB') as DOB,
    JSON_Value(p.value, '$.DeptID') as DEPTID,
    JSON_Value(p.value, '$.DName') as DName
FROM OPENJSON(@MyJSON, '$.EmployeeDepartment') as c
CROSS APPLY OPENJSON(c.value, '$.DEPT') as p

Reading JSON elements into separate columns

You can specify the child elements with the full path by using the dollar sign “$” inside the WITH() clause to segregate the data into the separate columns.

DECLARE @MyJSON NVARCHAR(4000) = N '{
"EmployeeDepartment": [{
    "EmployeeID": "E0001",
    "FirstName": "Prashanth",
    "LastName": "Jayaram",
    "DOB": "1983-02-03",
    "DEPT": [{
        "DeptID": "D1",
        "DName": "Ducks"
    }, {
        "DeptID": "D2",
        "DName": "Blues"
    }]
}, {
    "EmployeeID": "E0002",
    "FirstName": "Prarthana",
    "LastName": "Prashanth",
    "DOB": "2015-07-06",
    "DEPT": [{
        "DeptID": "D3",
        "DName": "Red Wings"
    }]
}, {
    "EmployeeID": "E0003",
    "FirstName": "Pravitha",
    "LastName": "Prashanth",
    "DOB": "2015-07-06",
    "DEPT": [{
        "DeptID": "D3",
        "DName": "Red Wings"
    }, {
        "DeptID": "D4",
        "DName": "Green Bird"
    }]
}]
}
'
SELECT
EmployeeID,
FirstName,
DOB,
Dept1, DName1,
Dept2, DName2
FROM OPENJSON(@MyJSON, '$.EmployeeDepartment')
WITH(EmployeeID varchar(20)
    '$.EmployeeID', FirstName varchar(20)
    '$.FirstName', DOB varchar(20)
    '$.DOB', Dept1 varchar(20)
    '$.DEPT[0].DeptID', Dname1 varchar(20)
    '$.DEPT[0].DName', Dept2 varchar(20)
    '$.DEPT[1].DeptID', Dname2 varchar(20)
    '$.DEPT[1].DName') AS EMP

Conclusion

SQL 2016 contains some very powerful JSON constructs. Mixing the power of the relational databases with the flexibility of JSON offers many benefits from the point of Migration, Integration, and Deployment.
The powerful JSON SQL constructs enable to query and analyze JSON data as well as transform JSON to the relational data and the relational data to JSON.

There are plenty of examples and resources, which are 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, which you need with one read operation or where you can insert complex structure 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 new 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.

References

• https//blogs.technet.microsoft.com/dataplatforminsider/2016/01/06/json-in-sql-server-2016-part-2-of-4/
• https//adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html
• http//www.w3schools.com/js/js_json_syntax.asp
• https//www.codeproject.com/Articles/1046120/Friday-the-th-JSON-is-coming-to-SQL-Server