Support, Functionality, and Limitations of JSON in SQL Server 2016

As JSON continues to increase in popularity, support from third-party products is burgeoning as well. According to the Microsoft team, JSON support was one of the most requested features on Microsoft connect prior to its official announcement. While some JSON functionality is available in SQL Server 2016, significant limitations may hamper development and storage efforts.

The Basics of JSON

JSON is a language-independent format to store objects in attribute-value pairs. Due to its compactness and flexibility, it is increasingly replacing XML. Its simplicity makes it superior to XML as a data-interchange format; although JSON lacks the structured overhead of XML, that feature is unimportant for data interchange.

Here’s a simple JSON example, taken from JSON.org. It describes the representation of a menu, using attribute:value pairs to describe the underlying data:

{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}

Menu is the schema at the root of the definition – typically, this entire representation would be referred to as the menu schema. Other schemas, such as menuitem, are considered nested schemas.

JSON in SQL Server

Unlike XML support – in which SQL Server uses a specific datatype – JSON data is stored as VARCHAR. This implementation has been widely derided by the SQL Server community, as it greatly limits or eliminates the ability to index and rapidly query JSON data. The same limitations and performance degradations of string operations will be present. For simple storage and retrieval, however, the current JSON support is sufficient.

Converting Relational Data to JSON

SQL Server 2016 introduces the FOR JSON clause, which represents stored data in column order. The simple example below creates a table of employee data, then represents it as a JSON schema:

CREATE TABLE JSONEmployee (EmployeeID INT IDENTITY, EmployeeFirstName Varchar(64), EmployeeLastName 
Varchar(64), EmployeeDOB Date)
GO

INSERT JSONEmployee (EmployeeFirstName, EmployeeLastName, EmployeeDOB)
 VALUES ('Paul', 'Kariya', '1974-10-16'), ('Steve', 'Yzerman', '1904-03-02')
GO

Select EmployeeFirstName, EmployeeLastName, EmployeeDOB
From JSONEmployee
FOR JSON AUTO
GO

Output:

[{"EmployeeFirstName":"Paul","EmployeeLastName":"Kariya","EmployeeDOB":"1974-10-16"},
{"EmployeeFirstName":"Steve","EmployeeLastName":"Yzerman","EmployeeDOB":"1904-03-02"}]

A slightly more complex example allows for multiple values in a nested schema, DEPT (Department):

CREATE TABLE EmployeeDepartment (EmployeeDepartmentID INT IDENTITY, EmployeeID INT, 
EmployeeDepartment Varchar(32))
GO

INSERT EmployeeDepartment (EmployeeID, EmployeeDepartment)
VALUES (1, 'Ducks'), (1, 'Blues'), (2, 'Red Wings')
GO


Select EmployeeFirstName, EmployeeLastName, EmployeeDOB,
(Select EmployeeDepartment from EmployeeDepartment DEPT Where DEPT.EmployeeID = EMP.EmployeeID
FOR JSON AUTO) DEPT
From JSONEmployee EMP
FOR JSON AUTO
GO

Output:

[{"EmployeeFirstName":"Paul","EmployeeLastName":"Kariya","EmployeeDOB":"1974-10-16",
"DEPT":[{"EmployeeDepartment":"Ducks"},{"EmployeeDepartment":"Blues"}]},
{"EmployeeFirstName":"Steve","EmployeeLastName":"Yzerman","EmployeeDOB":"1904-03-02",
"DEPT":[{"EmployeeDepartment":"Red Wings"}]}]

In the output, Paul has two assigned departments.

The above example uses FOR JSON AUTO to format the results based on the structure of the select statement. FOR JSON PATH allows you to have finer control over the output format. You can define the JSON structure in the query by defining a column alias with a dot- separated (.) value. In the next code block, EmployeeDepartment is formatted at the root:

Select EmployeeFirstName as 'Employee.FirstName', 
EmployeeLastName as 'Employee.LastName', 
EmployeeDOB as 'Employee.DOB',
(Select EmployeeDepartment from EmployeeDepartment DEPT Where DEPT.EmployeeID = EMP.EmployeeID
FOR JSON AUTO) as as 'Employee.DEPT'
From JSONEmployee EMP
FOR JSON PATH, root ('EmployeeDepartment')
GO

{"EmployeeDepartment":
[{"Employee" : 
      { "FirstName":"Paul",
        "LastName":"Kariya",
         "DOB":"1974-10-16",
         "DEPT":[{"EmployeeDepartment":"Ducks"},{"EmployeeDepartment":"Blues"}]
       }
   },
 {"Employee" : 
       { "FirstName":"Steve",
         "LastName":"Yzerman",
         "DOB":"1904-03-02",
         "DEPT":[{"EmployeeDepartment":"Red Wings"}]
        }
   }
]}

FOR JSON PATH can be used to display JSON text in various ways when AUTO is insufficient.

Parsing JSON Data in SQL Server

SQL Server 2016 introduces JSON_QUERY and JSON_VALUE, two functions that return values from JSON text. JSON_QUERY is used to return objects or arrays, while JSON_VALUE returns a scalar value.

DECLARE @MyJSON NVARCHAR(4000) = 
N'{
    "parentschema":{  
      "type":1,
      "address":{  
        "town":"Louisville",
        "county":"Boulder",
        "country":"USA"
      },
      "tags":["Snow", "Breweries"]
   },
   "type":"Location"
}'


SELECT JSON_QUERY(@MyJSON, '$.parentschema.address') AS Address
SELECT JSON_VALUE(@MyJSON, '$.parentschema.address') AS Address

SELECT JSON_QUERY(@MyJSON, '$.parentschema.type') AS Address
SELECT JSON_VALUE(@MyJSON, '$.parentschema.type') AS Address

Output:

{  
        "town":"Louisville",
        "county":"Boulder",
        "country":"USA"
      }
NULL
NULL
1

In the above example, the address contains multiple values; JSON_QUERY will return the array while JSON_VALUE returns a NULL. Conversely, JSON_VALUE returns a 1 for the scalar ‘type’, while JSON_QUERY returns a NULL.

Both JSON_QUERY and JSON_VALUE can be configured to return an error rather than a NULL by specifying the strict keyword:

SELECT JSON_VALUE(@MyJSON, 'strict $.parentschema.address') AS Address

Output:

Msg 13608, Level 16, State 1, Line 76
Property cannot be found in specified path.

Testing JSON Data

The SQL function ISJSON returns a “1” for valid JSON data, or a “0” if the string is not JSON data.

DECLARE @jsonInfo VARCHAR(MAX)
SET @jsonInfo = N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"Bristol",
        "county":"Avon",
        "country":"England"
      },
      "tags":["Sport", "Water polo"]
   },
   "type":"Basic"
}'


SELECT ISJSON(@jsonInfo)

Set @JsonInfo = 'This does not look very JSON'

SELECT ISJSON(@jsonInfo)

Output:

1
0

Converting JSON Data to a Relational Format

Arguably the most useful JSON function, OPENJSON converts JSON text to a relational format for storage. The key, value, and datatype are returned from the function:

DECLARE @MyJSON NVARCHAR(4000) = 
N'{
    "parentschema":{  
      "type":1,
      "address":{  
        "town":"Louisville",
        "county":"Boulder",
        "country":"USA"
      },
      "tags":["Snow", "Breweries"]
   },
   "LocationType":"Location",
   "WeatherType":"Cold"
}'

Select * from  OPENJSON (@MyJSON)

This example uses a default schema, but an explicit schema with required datatypes can also be expressed.

JSON and SQL Server: What Will the Future Bring?

While there are some helpful functions for manipulating JSON text in SQL Server 2016, the decision to implement JSON over VARCHAR may limit its overall usefulness. If there is significant demand for increased JSON support, Microsoft may elect to implement a JSON datatype in the near future. For now, SQL Server 2016 has enough compatibility to support basic storage and manipulation.