JSON Support Is The Best New Developer Feature in SQL 2016 — Overview Part 1: Parsing JSON

As a developer my favorite new feature of SQL Server 2016 is JSON support.

I love JSON in SQL because I already love JSON everywhere outside of SQL: it uses much less space than XML for serializing data, it’s what most apps are now using for API communication, and when web developing I love that it is already valid JavaScript (no need to deserialize!).

I had this same type of excitement for XML in SQL Server, but after using it the excitement quickly turned into disappointment: having to constantly use the XML datatype was inconvenient (when most XML data I used was already stored in nvarchar(max) columns) and I never found the syntax of OPENXML() to be that intuitive.

Everything I’ve done with JSON in SQL Server 2016 so far has been great. I’ve already been storing persistent JSON in SQL, so being able to manipulate JSON within SQL is even better. In this series of posts I will go over the various functionalities of using JSON in SQL Server 2016:

Part 1 — Parsing JSON

What is JSON?

JavaScript Object Notation (JSON) “ is a lightweight data-interchange format.

My simple, mostly caveat-free* explanation is that it is a format for storing object data in JavaScript. It’s lightweight and easy to read, so it’s used in lots of applications that aren’t just JavaScript (although it’s especially easy to consume in JavaScript because it is JavaScript*).

*Caveats? See http://stackoverflow.com/a/383699

So what’s JSON look like? The JSON below represents the current inventory of cars in my garage. It shows I have two cars as well as some of their attributes:

Strict versus Lax mode

For any of the SQL JSON functions (OPENJSON(), JSON_VALUE(), JSON_QUERY(), andJSON_MODIFY()) you can specify whether invalid JSON paths will return NULL or an error. The default value is lax, which will return a NULL for non-existing JSON paths, whereas strict will return an error message.

The lax modifier is helpful when writing queries that check to see if values exist in a JSON object while the strict modifier works great for error checking and and validation.

ISJSON()

A simple function for verifying whether an inputted string is valid JSON. This is great to use in order to validate JSON formatting before running any of remaining functions in this post.

JSON_VALUE()

Extracts a scalar value from a JSON string. This function needs to be able to parse the value, so it will not parse out complex objects like arrays.

JSON_VALUE() is great for accessing operational data that might be using JSON to store multiple attributes for a single entry.

JSON_QUERY()

JSON_QUERY() is meant to work for all of the datatypes that JSON_VALUE() doesn’t know how to return: basically JSON_QUERY() returns JSON string representations of complex JSON objects like arrays.

It’s possible to use JSON_QUERY() along with JSON_VALUE() to essentially extract any type of data from JSON, whether it’s a simple or complex object datatype.

OPENJSON()

While JSON_VALUE() extracts singular scalar values and JSON_QUERY() extracts singular JSON strings, the OPENJSON() function extracts result sets from a JSON string. In addition to the extracted value, OPENJSON() outputs the order of JSON objects as well as their datatypes. OPENJSON() will also output string representations of JSON arrays instead of just displaying NULL, similar to JSON_QUERY().

The flexibility of OPENJSON() makes it possible to extract any values from JSON data, especially when combining OPENJSON() with JSON_VALUE(). The examples below show how to parse out a scalar value from complex JSON objects (like arrays). Note that using the WITH option gives us a lot more flexibility with how we can format our output result.


These JSON functions should help you parse any JSON data you encounter in SQL server (as long as it’s valid and stored as nvarchar). Stay tuned over the next few weeks as we look at other SQL JSON functions that will help us create data, modify data, and compare SQL’s JSON performance to other languages.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Leave a Reply

Your email address will not be published. Required fields are marked *