SQL Server 2016: JSON Support

SQL Server 2016 is expected to offer native support for working with JSON. The first iteration of this support will be released as part of SQL Server 2016’s CTP 2. Also known as a “Community Tech Preview”, a CTP is Microsoft’s equivalent to a public alpha test wherein developers are allowed to recommend changes to the technology.

Exporting JSON

The main CTP 2 feature is the ability to “format and export” JSON. This is done by appending a FOR JSON clause to the end of a SELECT statement. It is based on the FOR XML clause and, like it, allows for both automatic and semi-manual formatting of the resulting JSON string.

Microsoft expects this syntax to be equivalent to PostgreSQL’s row_to_json and json_object functions.

Transforming JSON

The bulk of the feature set will be released in CTP 3. The first of these features is the FROM OPENJSON clause, which is a Table-Value Function (TFV) that accepts a JSON string as its input. It also requires a path to an array or object in the JSON data to be parsed.

By default, OPENJSON comes back as a set of key-value pairs, but you can supply a schema using a WITH clause. Since JSON doesn’t support dates or integers (they are represented as strings and doubles respectively), the WITH clause can reduce the amount of type conversions needed later.

Here is an example from Jovan Popovic’s article on JSON support.

DECLARE @JSalestOrderDetails nVarCar(2000) = N '{"OrdersArray": [
{"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200},
{"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100},
{"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250},
{"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200}
]}';

SELECT Number, Customer, Date, Quantity
FROM OPENJSON (@JSalestOrderDetails, '$.OrdersArray')
WITH (
    Number varchar(200),
    Date datetime,
    Customer varchar(200),
    Quantity int
) AS OrdersArray

To do the same in PostgreSQL, Microsoft claims that you need to use a combination of then json_each, json_object_keys, json_populate_record, and json_populate_recordset functions.

Storing JSON

As you can see, JSON data is stored as an nVarChar variable. This is also the case when storing JSON data inside table columns. Microsoft has several reasons for this:

• Migration - We found that people already store JSON as text, so they would need to change database schema and reload data to use our new features if we introduce a separate JSON type. In our implementation, developers can use JSON functionalities without any change in database.
• Cross feature compatibility – NVARCHAR is supported in all SQL Server components, so JSON will also be supported everywhere. You can put JSON in Hekaton, temporal or column store tables, apply standard security policies including row level security, use standard B-Tree and FTS indexes, use JSON as a parameter or return value of procedures and UDFs, etc. You don’t need to think does JSON works with feature X – if NVARCHAR works with feature X then JSON will also work. Again there are some constraints – Hekaton and column store do not support LOB values so you can put only small JSON documents there. However, once we add LOB support in Hekaton and column store you will be able to store large JSON documents everywhere.
• Client-side support – Currently we don’t have standardized JSON object type in client apps (something like XmlDom object). Web and mobile application, and JavaScript clients will naturally use JSON text and parse it with their native parsers. In JavaScript, a type that represents JSON is object. It is unlikely that they will implement some proxy of JSON type that exists only in a few RDBMS. In C#.Net, a lot of developers use JSON.Net parser with built in JObject or JArray type; however it is not a standard and probably it will not be part of ADO.NET. Even in that case we believe that C# apps will accept plain strings from database layer and parse it with their favorite parser. We are not talking only about apps. If you try to use JSON column in SSIS/SSRS, Tableau, Informatica ETL, they will still see it as text. We believe that even if we add a JSON type it will be represented as string outside SQL Server and parsed with some custom parser if needed. Therefore, we have not found any major reason to implement it as native JSON type.

It is probably a good idea to use the new ISJSON function as a check constraint on your JSON-containing nVarChar columns. If you don’t, you risk data corruption from flawed client applications inserting unparsable strings.

Querying JSON

To directly query JSON for scalar values, you can use the JSON_VALUE function. This function uses a JavaScript like notation to locate values within the JSON object. It uses the $ symbol as the object root, dot-notation for properties, and brackets for array indexes. The PostgreSQL equivalent is json_extract_path_text.

JSON Indexes

JSON data cannot be directly indexed, but you can get the same effect for scalar data with minimal effort. Simply create a computed column using the JSON_VALUE function, then place an index on that.

“Missing” Features

Microsoft’s plan for JSON doesn’t include everything developers want. Many believe that a dedicated JSONB style column would offer better performance. Others want support for patching JSON data instead of having to replace the document as a whole.