SQL Server 2016: JSON Integration

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.
• Data is separated by commas. {“key””value”},{“key””value”}.
• Curly braces hold the objects. {“key”{“key””value”}}.
• Square brackets hold 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

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:

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

[{ 
    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 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.

• 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 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:

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)

(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. 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:

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.

[{ 
    "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) 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).

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))

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.
• 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 ) 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 objects in the input text.
• ‘$.property1’ – references property1 in JSON objects.
• ‘$[4]’ – references the fifth element in JSON array (indexes are counted from 0 like in JavaScript).
• ‘$.property1.property2.array1[5].property3.array2[15].property4’ – references a 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 objects. 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 given above also contain sample data that represents nested objects.

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

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.

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 into separate rows using JSON_Value() with the 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

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 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.