SQL Server 2016 is around the corner. XML has been supported since SQL Server 2008. In SQL Server 2016 although JSON is not natively available as a data type, but, there are boat-load of functions and keywords that lets you utilize JSON data.
Data Type
Data type wise, JSON is represented by NVARCHAR data type. Main reason is people have been storing JSON data for quite some time now and migration will be a lot easier if data types aren’t required to be changed.
Querying a relational table and returning result in JSON format
If you want to query some existing table and return result in JSON format then you can use similar syntax that’s used for returning data in XML format.
SELECT col1, col2 FROM table FOR JSON ;
If you use AUTO switch, it will use default behavior for returning column names and arrays.
For having more control over names and arrays you can use PATH switch
So, essentially you are converting data to JSON format at database layer rather than at service layer.
Querying JSON data and returning result in a relational table format
If you already have JSON string and you want to convert it to a relational table then OPENJSON can be used, just like OPENROWSET can be used for querying a CSV / Excel file.
Let’s assume we have employees variable that contains collection of employees.
Let’s say we have employees data stored in a variable (or some table’s column) like this:
DECLARE @employees nvarchar(100)
SET @employees = “{ ‘employees’:[
{‘EmployeeId’:1, ‘EmployeeName’:’Bob’},
{‘EmployeeId’:2, ‘EmployeeName’:’Alice’}
]}“
The following query will let us query JSON data as a relational table:
SELECT EmployeeId, EmployeeName
FROM OPENJSON (@employees, ‘$.employees’)
WITH
(
EmployeeId int,
EmployeeName varchar(20)
) AS EmployeesArray
Other JSON related built-in functions
ISJSON(json_data)
It’s a useful function for validating JSON.
JSON_VALUE(json_data, path)
This function returns a scalar value (some specific column/property’s value) from JSON data.
e.g.
JSON_VALUE(@employees, ‘$.EmployeeId’) returns employee id of first record.
JSON_VALUE(@employee, ‘$.employeeId’) returns employee id of the record ASSUMING that @employee is a single record / object and not an array.
Indexing on JSON data
Since there’s no new data type for JSON data, so you can use regular indexes for your JSON based columns.
