SQL Server 2016: JSON integration
Abstract
This article helps to explain the application of JSON data and the use of new SQL 2016 constructs that enable an integration of JSON data into a relational schema.
The idea behind this article is to list as many examples of JSON nested elements, an inclusion of sample data, relational data conversion to JSON data, JSON to the relational data, conversion of JSON elements into separate columns and representing the same data into separate rows.
Introduction
The 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 with 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 refer 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.
The 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"} ]}
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
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.
- OPENJSON() - Table valued function parses JSON text and returns row set view of JSON.
- 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.
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 separator
For example,
Lets create a sample table 'EMP' and 'DEPT' and insert few rows in it
CREATE TABLE EMP
(EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2))
INSERT INTO EMP VALUES
(7782, 'CLARK', ``'MANAGER', 7839, '9-JUN-1981', ``2450, NULL, ``10)
INSERT INTO EMP VALUES
(7788, 'SCOTT', ``'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20)
INSERT INTO EMP VALUES
(7839, 'KING', ``'PRESIDENT', NULL, ``'17-NOV-1981', ``5000, NULL, ``10)
INSERT INTO EMP VALUES
(7844, 'TURNER', ``'SALESMAN', 7698, '8-SEP-1981', ``1500, 0, 30)
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10)
CREATE TABLE DEPT
(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) )
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', ``'NEW YORK')
INSERT INTO DEPT VALUES (20, 'RESEARCH', ``'DALLAS')
INSERT INTO DEPT VALUES (30, 'SALES', ``'CHICAGO')
INSERT INTO DEPT VALUES (40, 'OPERATIONS', ``'BOSTON')
The output of "FOR JSON AUTO" option is given below
SELECT * FROM EMP E INNER JOIN DEPT D ``ON E.DEPTNO=D.DEPTNO
WHERE D.DEPTNO=10
FOR JSON AUTO
This option formats the JSON document automatically based upon the columns provided in the Query.
"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 with PATH option creates wrapper class 'department' and nest properties deptno,dname,location
The JSON Data Transformation
Transform the below sample data to JSON
| ID | Type | Name | Batter | Topping |
| 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 few more entries are added for the id 0002. In the above sample data, we can see there is four batter types and 7 toppings to prepare 28(1*4*7=28)different types of cake.similarly. for id 0002, 4 batter types and 3 toppings used to prepare 12(1*4*3) types of cake.
[{
"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
The 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 a 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 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 object. One simple example of a query, where these built-in functions are used is 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 are 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. It is flexible because of simple syntax and lesser overhead to maintain and manage the JSON data.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
- https//www.codeproject.com/Articles/1046120/Friday-the-th-JSON-is-coming-to-SQL-Server