{"id":2127,"date":"2015-12-15T00:00:00","date_gmt":"2015-12-14T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/json-support-in-sql-server-2016\/"},"modified":"2026-09-21T11:21:19","modified_gmt":"2026-09-21T11:21:19","slug":"json-support-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/json-support-in-sql-server-2016\/","title":{"rendered":"JSON support in SQL Server 2016"},"content":{"rendered":"\n
\n

SQL Server has supported JSON since SQL Server 2016, and the functions have not changed since: FOR JSON turns a query result into JSON (AUTO for a shape derived from the query, PATH for one you control); OPENJSON shreds a JSON string into rows and columns; ISJSON tests whether a string is valid JSON; JSON_VALUE extracts a scalar and JSON_QUERY extracts an object or array; and JSON_MODIFY changes a value in place. <\/strong><\/p>\n

JSON is stored in NVARCHAR columns (SQL Server 2025 adds a native json type<\/a>). This article works through each function with examples; for the design question – when to store JSON at all and how to index it – see Ed Pollack’s guide to storing and parsing JSON<\/a>.<\/strong><\/p>\n

SQL Server 2016 is finally adding support for JSON, a lightweight format for exchanging data between different source types, similar to how XML is used. JSON, short for JavaScript Object Notation, is based on a subset of the JavaScript programming language and is noted for being human readable and easy for computers to parse and generate.<\/p>\n

According to Microsoft, it is one of the most highly ranked requests on the Microsoft connect site and so for many, its inclusion in SQL Server is welcome news. That is, unless you were expecting the same sort of robust support we’ve seen with XML. SQL Server 2016 does not approach JSON with such vehemence, nor does it match what you’ll find in products such as PostgreSQL.<\/p>\n

SQL Server 2016 includes no JSON-specific data type and consequently none of the kinds of methods available to the XML<\/strong> data type. SQL Server 2016 continues to use the NVARCHAR<\/strong> type to store JSON data. However, it does provide several important T-SQL language elements that make working with JSON much easier than it has been in the past, so Microsoft is at least moving in the right direction, even if it still has some catching up to do.<\/p>\n

Getting to know JSON<\/h2>\n

Although JSON is a bit more complex than what we’ll cover here, it can help to have a basic understanding of what makes up a JSON code snippet before starting in on the SQL Server support. At its most basic, a JSON snippet can contain objects, arrays, or both. An object is an unordered collection of one or more name\/value pairs (properties), enclosed in curly braces, as shown in the following example:<\/p>\n

\t{\"FirstName\":\"Terri\", \"Current\":true, \"Age\":42, \"Phone\":null}\n<\/pre>\n
For each property, the name component (FirstName<\/code><\/strong>, Current<\/code><\/strong>, Age<\/code><\/strong>, and Phone<\/code><\/strong>) is enclosed in double quotes and followed by a colon. The name component, sometimes referred to as the key,<\/em> is always a string. The property’s value follows slightly different rules. If the value is a string, you should enclose it in double quotes. If it is a numeric value, Boolean value (true<\/code><\/strong> or false<\/code><\/strong>), or null<\/code><\/strong> value, do not enclose it in quotes.<\/p>\n
An array is simply an ordered collection of values, enclosed in square brackets, as in the following example:<\/p>\n
\t[\"Terri\", true, 42, null]\n<\/pre>\n
An array supports the same types of values as an object: string, number, true<\/code><\/strong>, false<\/code><\/strong>, or null<\/code><\/strong>. In addition, both objects and arrays can contain other objects and arrays as their values, providing a way to nest structures, as shown in the following example:<\/p>\n
{\n   \"Employees\":[\n      {\n         \"Name\":{\n            \"First\":\"Terri\",\n            \"Middle\":\"Lee\",\n            \"Last\":\"Duffy\"\n         },\n         \"PII\":{\n            \"DOB\":\"1971-08-01\",\n            \"NatID\":\"245797967\"\n         },\n         \"LoginID\":\"adventure-works\\\\terri0\"\n      },\n      {\n         \"Name\":{\n            \"First\":\"Roberto\",\n            \"Middle\":null,\n            \"Last\":\"Tamburello\"\n         },\n         \"PII\":{\n            \"DOB\":\"1974-11-12\",\n            \"NatID\":\"509647174\"\n         },\n         \"LoginID\":\"adventure-works\\\\roberto0\"\n      }\n   ]\n}<\/pre>\n
At the top level, we have a JSON object that includes a single property. The property’s name is Employees<\/code><\/strong>, and the value is an array, which contains two values. Each array value is a JSON object that includes the Name<\/code><\/strong>, PII<\/code><\/strong>, and LoginID<\/code><\/strong> properties. The Name<\/code><\/strong> and PII<\/code><\/strong> values are also JSON objects, which contain their own name\/value pairs.<\/p>\n
As we work through the examples in this article, you’ll get a better sense of how these various components work.<\/p>\n
Formatting query results as JSON<\/h2>\n
One of the JSON-related features supported in SQL Server 2016 is the ability to return data in the JSON format, which we do by adding the FOR JSON<\/strong><\/code> clause to a SELECT<\/strong><\/code> statement. We’ll explore the basics of how to use a FOR JSON<\/strong><\/code> clause to return data in the JSON format, using either the AUTO<\/strong><\/code> argument or the PATH<\/code> argument.<\/p>\n
First, however, we need some data on which to work. The following SELECT<\/strong><\/code> statement retrieves two rows from the vEmployee<\/code><\/strong> view in the AdventureWorks2016CTP3<\/code><\/strong> database:<\/p>\n
USE AdventureWorks2016CTP3;\ngo\n\nSELECT FirstName, MiddleName, LastName, \n  EmailAddress, PhoneNumber\nFROM HumanResources.vEmployee\nWHERE BusinessEntityID in (2, 3);<\/pre>\n
It returns the following results, although you might see some differences with the final product, since the data and examples are based on the CTP 3 release of SQL Server 2016:<\/p>\n\n\n\n\n\n\n
FirstName<\/td>\nMiddleName<\/td>\nLastName<\/td>\nEmailAddress<\/td>\nPhoneNumber<\/td>\n<\/tr>\n<\/thead>\n
Terri<\/td>\nLee<\/td>\nDuffy<\/td>\nterri0@adventure-works.com<\/td>\n819-555-0175<\/td>\n<\/tr>\n
Roberto<\/td>\nNULL<\/td>\nTamburello<\/td>\nroberto0@adventure-works.com<\/td>\n212-555-0187<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
AUTO mode<\/h2>\n
To return these results as JSON, to support a specific application, we simply add the FOR JSON<\/strong><\/code> clause to the statement, as shown in the following example.<\/p>\n
SELECT FirstName, MiddleName, LastName, \n  EmailAddress, PhoneNumber\nFROM HumanResources.vEmployee\nWHERE BusinessEntityID in (2, 3)\nFOR JSON AUTO;<\/pre>\n
Notice that the clause includes the AUTO<\/strong><\/code> argument, which indicates that the results should be returned in AUTO<\/strong><\/code> mode. When you specify this mode, the database engine automatically determines the JSON<\/strong><\/code> format, based on the order of the columns in the SELECT<\/strong><\/code> list and the tables in the FROM<\/strong><\/code> clause. In this case, the FOR JSON AUTO<\/strong><\/code> clause causes the SELECT<\/strong><\/code> statement to return the following results.<\/p>\n
\t[{\"FirstName\":\"Terri\",\"MiddleName\":\"Lee\",\"LastName\":\"Duffy\",\"EmailAddress\":\"terri0@adventure-works.com\",\"PhoneNumber\":\"819-555-0175\"},{\"FirstName\":\"Roberto\",\"LastName\":\"Tamburello\",\"EmailAddress\":\"roberto0@adventure-works.com\",\"PhoneNumber\":\"212-555-0187\"}]\n<\/pre>\n
From these results, you might be able to see that the JSON output includes an array that contains two values, with each value a JSON object. Not surprisingly, as the results become more involved, it becomes more difficult to read them. In such cases, you can use a local or online JSON formatter\/validator to turn the JSON snippet into something more readable. For example, I fed the previous results into the formatter at https:\/\/jsonformatter.curiousconcept.com\/<\/a> and came up with the following JSON:<\/p>\n
[\n   {\n      \"FirstName\":\"Terri\",\n      \"MiddleName\":\"Lee\",\n      \"LastName\":\"Duffy\",\n      \"EmailAddress\":\"terri0@adventure-works.com\",\n      \"PhoneNumber\":\"819-555-0175\"\n   },\n   {\n      \"FirstName\":\"Roberto\",\n      \"LastName\":\"Tamburello\",\n      \"EmailAddress\":\"roberto0@adventure-works.com\",\n      \"PhoneNumber\":\"212-555-0187\"\n   }\n]<\/pre>\n
As you can see, it is now much easier to see our top-level array and the two object values it contains. Each object corresponds to a row returned by the SELECT<\/code><\/strong> statement. Going forward, I’ll show only the formatter-fed results so they’re more readable, but know that SQL Server returns the data as a single-line value, without all the whitespace and line breaks, as you saw above.<\/p>\n
Now that you’ve gotten a taste of the FOR JSON AUTO<\/strong><\/code> clause, let’s look at what happens when we join tables:<\/p>\n
SELECT e.BirthDate, e.NationalIDNumber, e.LoginID,\n  p.FirstName, p.MiddleName, p.LastName\nFROM HumanResources.Employee e INNER JOIN Person.Person p\n  ON e.BusinessEntityID = p.BusinessEntityID\nWHERE e.BusinessEntityID in (2, 3)\nFOR JSON AUTO;<\/pre>\n
As our SELECT<\/strong><\/code> statement becomes more complex, so too does the JSON output, as shown in the following results:<\/p>\n
[ \n   { \n      \"BirthDate\":\"1971-08-01\",\n      \"NationalIDNumber\":\"245797967\",\n      \"LoginID\":\"adventure-works\\\\terri0\",\n      \"p\":[\n         {\n            \"FirstName\":\"Terri\",\n            \"MiddleName\":\"Lee\",\n            \"LastName\":\"Duffy\"\n         }\n      ]\n   },\n   {\n      \"BirthDate\":\"1974-11-12\",\n      \"NationalIDNumber\":\"509647174\",\n      \"LoginID\":\"adventure-works\\\\roberto0\",\n      \"p\":[\n         {\n            \"FirstName\":\"Roberto\",\n            \"LastName\":\"Tamburello\"\n         }\n      ]\n   }\n]<\/pre>\n
The information from the Person<\/code><\/strong> table is now part of the p<\/strong> array, which itself is one of the values in the parent object. As you’ll recall, AUTO<\/strong><\/code> mode formats the results based on the order of the columns in the SELECT<\/strong><\/code> list and the tables in the FROM<\/strong><\/code> clause, so let’s mix up that column order:<\/p>\n
SELECT p.FirstName, p.MiddleName, p.LastName,\n  e.BirthDate, e.NationalIDNumber, e.LoginID\nFROM HumanResources.Employee e INNER JOIN Person.Person p\n  ON e.BusinessEntityID = p.BusinessEntityID\nWHERE e.BusinessEntityID in (2, 3)\nFOR JSON AUTO;<\/pre>\n
Now the SELECT<\/strong><\/code> statement will return the JSON with the data from the Employee<\/code><\/strong> table treated as the nested object:<\/p>\n
[\n   {\n      \"FirstName\":\"Terri\",\n      \"MiddleName\":\"Lee\",\n      \"LastName\":\"Duffy\",\n      \"e\":[\n         {\n            \"BirthDate\":\"1971-08-01\",\n            \"NationalIDNumber\":\"245797967\",\n            \"LoginID\":\"adventure-works\\\\terri0\"\n         }\n      ]\n   },\n   {\n      \"FirstName\":\"Roberto\",\n      \"LastName\":\"Tamburello\",\n      \"e\":[\n         {\n            \"BirthDate\":\"1974-11-12\",\n            \"NationalIDNumber\":\"509647174\",\n            \"LoginID\":\"adventure-works\\\\roberto0\"\n         }\n      ]\n   }\n]<\/pre>\n
As you can see, we have two e<\/strong> arrays, embedded in the outer objects. We can continue to play around with our SELECT<\/strong><\/code> statement to try to get closer to the JSON results we want, or we can instead use the PATH<\/code><\/strong> mode, which gives us full control over the format of the JSON output. For all but the most basic SELECT<\/strong><\/code> statements, you’ll likely want to use the PATH<\/code><\/strong> mode.<\/p>\n
PATH mode<\/h2>\n
To use the PATH<\/code><\/strong> mode, we start be specifying PATH<\/code><\/strong> in the FOR JSON<\/strong><\/code> clause, rather than AUTO<\/strong><\/code>, as shown in the following example:<\/p>\n
SELECT p.FirstName, p.MiddleName, p.LastName,\n  e.BirthDate, e.NationalIDNumber, e.LoginID\nFROM HumanResources.Employee e INNER JOIN Person.Person p\n  ON e.BusinessEntityID = p.BusinessEntityID\nWHERE e.BusinessEntityID in (2, 3)\nFOR JSON PATH;<\/pre>\n
When we switch to the PATH<\/code><\/strong> mode, the database engine flattens out our results and returns the data as two object values within a single array:<\/p>\n
[\n   {\n      \"FirstName\":\"Terri\",\n      \"MiddleName\":\"Lee\",\n      \"LastName\":\"Duffy\",\n      \"BirthDate\":\"1971-08-01\",\n      \"NationalIDNumber\":\"245797967\",\n      \"LoginID\":\"adventure-works\\\\terri0\"\n   },\n   {\n      \"FirstName\":\"Roberto\",\n      \"LastName\":\"Tamburello\",\n      \"BirthDate\":\"1974-11-12\",\n      \"NationalIDNumber\":\"509647174\",\n      \"LoginID\":\"adventure-works\\\\roberto0\"\n   }\n]<\/pre>\n
Using the PATH<\/code><\/strong> mode in this way is fairly straightforward; however, this is PATH<\/code><\/strong> at its most basic. The mode lets us be far more specific. For example, we can control how the the database engine nests the JSON output by specifying column aliases that define the structure, as shown in the following SELECT<\/strong><\/code> clause:<\/p>\n
SELECT\n  p.FirstName AS [Name.First],\n  p.MiddleName AS [Name.Middle],\n  p.LastName AS [Name.Last],\n  e.BirthDate AS [PII.DOB], \n  e.NationalIDNumber AS [PII.NatID], \n  e.LoginID\nFROM HumanResources.Employee e INNER JOIN Person.Person p\n  ON e.BusinessEntityID = p.BusinessEntityID\nWHERE e.BusinessEntityID in (2, 3)\nFOR JSON PATH;<\/pre>\n
In this case, we are defining the Name<\/code><\/strong> object, which contains the First<\/code><\/strong>, Middle<\/code><\/strong>, and Last<\/code><\/strong> values; the PII<\/code><\/strong> object, which contains the DOB<\/code><\/strong> and NatID<\/code><\/strong> values; and the LoginID<\/code><\/strong> name\/value pair, as shown in the following results:<\/p>\n
[\n   {\n      \"Name\":{\n         \"First\":\"Terri\",\n         \"Middle\":\"Lee\",\n         \"Last\":\"Duffy\"\n      },\n      \"PII\":{\n         \"DOB\":\"1971-08-01\",\n         \"NatID\":\"245797967\"\n      },\n      \"LoginID\":\"adventure-works\\\\terri0\"\n   },\n   {\n      \"Name\":{\n         \"First\":\"Roberto\",\n         \"Last\":\"Tamburello\"\n      },\n      \"PII\":{\n         \"DOB\":\"1974-11-12\",\n         \"NatID\":\"509647174\"\n      },\n      \"LoginID\":\"adventure-works\\\\roberto0\"\n   }\n]<\/pre>\n
In some cases, you will want to add a single, top-level element to your JSON output to serve as a root. To do so, you must specify it as part of the FOR JSON<\/strong><\/code> clause, as shown in the following example:<\/p>\n
SELECT\n  p.FirstName AS [Name.First],\n  p.MiddleName AS [Name.Middle],\n  p.LastName AS [Name.Last],\n  e.BirthDate AS [PII.DOB], \n  e.NationalIDNumber AS [PII.NatID], \n  e.LoginID\nFROM HumanResources.Employee e INNER JOIN Person.Person p\n  ON e.BusinessEntityID = p.BusinessEntityID\nWHERE e.BusinessEntityID in (2, 3)\nFOR JSON PATH, ROOT('Employees');<\/pre>\n
To specify the root, we add the ROOT<\/code><\/strong> option to the FOR JSON<\/strong><\/code> clause and, in this case, name the root Employees<\/code><\/strong>, which gives us the following results:<\/p>\n
{\n   \"Employees\":[\n      {\n         \"Name\":{\n            \"First\":\"Terri\",\n            \"Middle\":\"Lee\",\n            \"Last\":\"Duffy\"\n         },\n         \"PII\":{\n            \"DOB\":\"1971-08-01\",\n            \"NatID\":\"245797967\"\n         },\n         \"LoginID\":\"adventure-works\\\\terri0\"\n      },\n      {\n         \"Name\":{\n            \"First\":\"Roberto\",\n            \"Last\":\"Tamburello\"\n         },\n         \"PII\":{\n            \"DOB\":\"1974-11-12\",\n            \"NatID\":\"509647174\"\n         },\n         \"LoginID\":\"adventure-works\\\\roberto0\"\n      }\n   ]\n}<\/pre>\n
If you compare these results to those from the previous example, you will see that the outer element has been changed from an array to an object that contains only the Employees<\/code><\/strong> property. The Employees<\/code><\/strong> value is now the array that was the outer element in the previous example.<\/p>\n
You might have also noticed that the second employee, Roberto, includes no middle name. That is because the MiddleName<\/strong> column in the source table is null. By default, the database engine does not include a JSON element whose value is null. However, you can override this behavior by adding the INCLUDE_NULL_VALUES<\/strong> option to the FOR JSON<\/strong><\/code> clause, as shown in the following SELECT<\/strong><\/code> statement:<\/p>\n
SELECT<\/p>\n
SELECT\n  p.FirstName AS [Name.First],\n  p.MiddleName AS [Name.Middle],\n  p.LastName AS [Name.Last],\n  e.BirthDate AS [PII.DOB], \n  e.NationalIDNumber AS [PII.NatID], \n  e.LoginID\nFROM HumanResources.Employee e INNER JOIN Person.Person p\n  ON e.BusinessEntityID = p.BusinessEntityID\nWHERE e.BusinessEntityID in (2, 3)\nFOR JSON PATH, ROOT('Employees'), INCLUDE_NULL_VALUES;<\/pre>\n
Now the results will show that Roberto’s middle name is null by assigning the null<\/code><\/strong> value to the Middle<\/strong> property:<\/p>\n
{\n   \"Employees\":[\n      {\n         \"Name\":{\n            \"First\":\"Terri\",\n            \"Middle\":\"Lee\",\n            \"Last\":\"Duffy\"\n         },\n         \"PII\":{\n            \"DOB\":\"1971-08-01\",\n            \"NatID\":\"245797967\"\n         },\n         \"LoginID\":\"adventure-works\\\\terri0\"\n      },\n      {\n         \"Name\":{\n            \"First\":\"Roberto\",\n            \"Middle\":null,\n            \"Last\":\"Tamburello\"\n         },\n         \"PII\":{\n            \"DOB\":\"1974-11-12\",\n            \"NatID\":\"509647174\"\n         },\n         \"LoginID\":\"adventure-works\\\\roberto0\"\n      }\n   ]\n}<\/pre>\n
There are, of course, other considerations to take into account when using this clause, so be sure to refer to SQL Server 2016 documentation. In the meantime, let’s look at how to convert a JSON snippet to traditional rowset data.<\/p>\n
Converting JSON to rowset data using the OPENJSON function<\/h2>\n
To return a JSON snippet as rowset data, we use the OPENJSON<\/code><\/strong> rowset function to convert the data to a relational format. The function returns three values:<\/p>\n
    \n
  • key:<\/strong> Property name within the object or index of the element within the array.<\/li>\n
  • value<\/code><\/strong>:<\/strong> Property value within the object or value of the array element specified by the index.<\/li>\n
  • type:<\/strong> Value’s data type, represented numerically, as described in the following table:<\/li>\n<\/ul>\n\n\n\n\n\n\n\n\n\n\n
    Numeric value<\/td>\nData type<\/td>\n<\/tr>\n<\/thead>\n
    0<\/td>\nnull<\/td>\n<\/tr>\n
    1<\/td>\nstring<\/td>\n<\/tr>\n
    2<\/td>\nint<\/td>\n<\/tr>\n
    3<\/td>\ntrue or false<\/td>\n<\/tr>\n
    4<\/td>\narray<\/td>\n<\/tr>\n
    5<\/td>\nobject<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    To test how the the OPENJSON<\/code><\/strong> function works, let’s assign a JSON snippet to a variable and then use the function to call the variable, as shown in the following example:<\/p>\n
    DECLARE @json NVARCHAR(MAX) = N'\n{\n  \"FirstName\":null,\n  \"LastName\":\"Duffy\",\n  \"NatID\":245797967,\n  \"Current\":false,\n  \"Skills\":[\"Dev\",\"QA\",\"PM\"],\n  \"Region\":{\"Country\":\"Canada\",\"Territory\":\"North America\"}\n}';\n\nSELECT * FROM OPENJSON(@json);<\/pre>\n
    The JSON snippet contains a single object that includes a property for each data type. The SELECT<\/strong><\/code> statement uses the OPENJSON<\/code><\/strong> rowset function within the FROM<\/strong><\/code> clause to retrieve the JSON data as a rowset, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n
    key<\/td>\nvalue<\/td>\ntype<\/td>\n<\/tr>\n<\/thead>\n
    FirstName<\/td>\nNULL<\/td>\n0<\/td>\n<\/tr>\n
    LastName<\/td>\nDuffy<\/td>\n1<\/td>\n<\/tr>\n
    NatID<\/td>\n245797967<\/td>\n2<\/td>\n<\/tr>\n
    Current<\/td>\nfalse<\/td>\n3<\/td>\n<\/tr>\n
    Skills<\/td>\n[“Dev”,”QA”,”PM”]<\/td>\n4<\/td>\n<\/tr>\n
    Region<\/td>\n{“Country”:”Canada”,”Territory”:”North America”}<\/td>\n5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Notice that the type<\/code><\/strong> column in the results identifies the data type for each value. As expected, the column shows the Skills<\/code><\/strong> value an array, with all of the array’s elements included in the results for that row. The same goes for the Region<\/code><\/strong> value, which is an object. The row includes all the properties within that object.<\/p>\n
    In some cases, you will want to return only the key<\/code><\/strong> and value<\/code><\/strong> columns, so you will need to specify those columns in your SELECT<\/strong><\/code> list:<\/p>\n
    SELECT [key], value\nFROM OPENJSON(@json);<\/pre>\n
    Notice that you must delimit the key<\/code><\/strong> column because Microsoft chose to return a column name that is also a T-SQL reserved keyword. As the following table shows, the results include only those two columns:<\/p>\n\n\n\n\n\n\n\n\n\n\n
    key<\/td>\nvalue<\/td>\n<\/tr>\n<\/thead>\n
    FirstName<\/td>\nNULL<\/td>\n<\/tr>\n
    LastName<\/td>\nDuffy<\/td>\n<\/tr>\n
    NatID<\/td>\n245797967<\/td>\n<\/tr>\n
    Current<\/td>\nfalse<\/td>\n<\/tr>\n
    Skills<\/td>\n[“Dev”,”QA”,”PM”]<\/td>\n<\/tr>\n
    Region<\/td>\n{“Country”:”Canada”,”Territory”:”North America”}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now let’s move on to a more complex JSON snippet, which we’ll use for the remaining examples in this article:<\/p>\n
    {\n   \"Employees\":[\n      {\n         \"Name\":{\n            \"First\":\"Terri\",\n            \"Middle\":\"Lee\",\n            \"Last\":\"Duffy\"\n         },\n         \"PII\":{\n            \"DOB\":\"1971-08-01\",\n            \"NatID\":\"245797967\"\n         },\n         \"LoginID\":\"adventure-works\\\\terri0\"\n      },\n      {\n         \"Name\":{\n            \"First\":\"Roberto\",\n            \"Middle\":null,\n            \"Last\":\"Tamburello\"\n         },\n         \"PII\":{\n            \"DOB\":\"1974-11-12\",\n            \"NatID\":\"509647174\"\n         },\n         \"LoginID\":\"adventure-works\\\\roberto0\"\n      }\n   ]\n}<\/pre>\n
    The JSON shown here comes from the output generated from the last example in the preceding section. As you’ll recall, the database engine actually outputs the JSON in a format much less readable than what is shown here, but it can be easier to work with when assigning the JSON to a variable. So that’s the approach we’ll take for the remaining examples:<\/p>\n
    DECLARE @json NVARCHAR(MAX) = N'{\"Employees\":[{\"Name\":{\"First\":\"Terri\",\"Middle\":\"Lee\",\"Last\":\"Duffy\"},\"PII\":{\"DOB\":\"1971-08-01\",\"NatID\":\"245797967\"},\"LoginID\":\"adventure-works\\\\terri0\"},{\"Name\":{\"First\":\"Roberto\",\"Middle\":null,\"Last\":\"Tamburello\"},\"PII\":{\"DOB\":\"1974-11-12\",\"NatID\":\"509647174\"},\"LoginID\":\"adventure-works\\\\roberto0\"}]}';\n<\/pre>\n
    If you plan to try out the next batch of examples, you can use this variable definition for each one, which avoids all the whitespace you get when you run the results through a parser. Now let’s use the OPENJSON<\/code><\/strong> function to convert the JSON in the variable:<\/p>\n
    SELECT [key], value\nFROM OPENJSON(@json);<\/pre>\n
    The example uses OPENJSON<\/code><\/strong> at its most basic, with no other parameters defined. As a result, the SELECT<\/strong><\/code> statement returns only a single row for the Employees<\/code><\/strong> array, as shown in the following table:<\/p>\n\n\n\n\n\n
    key<\/td>\nvalue<\/td>\n<\/tr>\n<\/thead>\n
    Employees<\/td>\n[{“Name”:{“First”:”Terri”,”Middle”:”Lee”,”Last”:”Duffy”},”PII”:{“DOB”:”1971-08-01″,”NatID”:”245797967″},”LoginID”:”adventure-works\\\\terri0″},{“Name”:{“First”:”Roberto”,”Middle”:null,”Last”:”Tamburello”},”PII”:{“DOB”:”1974-11-12″,”NatID”:”509647174″},”LoginID”:”adventure-works\\\\roberto0″}]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    To better control our results, we need to pass a second argument into the OPENJSON<\/code><\/strong> function. The argument is a JSON path that instructs the database engine on how to parse the data. For example, the following path instructs the database engine to return data based on the Employees<\/code><\/strong> property:<\/p>\n
    SELECT [key], value\nFROM OPENJSON(@json, '$.Employees');<\/pre>\n
    When you specify a JSON path, you start with a dollar sign ($<\/strong>) to represent the item as it exists in its current context. You then specify one or more elements as they appear hierarchically in the JSON snippet, using periods to separate the elements. In this case, the path specifies only the root element, Employees<\/code><\/strong>, giving us the results shown in the following table:<\/p>\n\n\n\n\n\n\n
    key<\/td>\nvalue<\/td>\n<\/tr>\n<\/thead>\n
    0<\/td>\n{“Name”:{“First”:”Terri”,”Middle”:”Lee”,”Last”:”Duffy”},”PII”:{“DOB”:”1971-08-01″,”NatID”:”245797967″},”LoginID”:”adventure-works\\\\terri0″}<\/td>\n<\/tr>\n
    1<\/td>\n{“Name”:{“First”:”Roberto”,”Middle”:null,”Last”:”Tamburello”},”PII”:{“DOB”:”1974-11-12″,”NatID”:”509647174″},”LoginID”:”adventure-works\\\\roberto0″}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    This time, we get a row for each element in the Employees<\/code><\/strong> array. If we want to break the results down even further, we must work down the hierarchy. For example, to reference an element within the Employees<\/code><\/strong> array, we must specify the element’s index, as it exists within the array. An array’s index is zero-based, which means the index count starts with 0, so if we want to retrieve the first element in the Employees<\/code><\/strong> array, we must specify 0<\/strong> after the root name, within square brackets, as shown in the following statement:<\/p>\n
    SELECT [key], value\nFROM OPENJSON(@json, '$.Employees[0]');<\/pre>\n
    The first element in the Employees<\/code><\/strong> array is a JSON object that contains three properties, so that is what the SELECT<\/strong><\/code> statement returns, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n
    key<\/td>\nvalue<\/td>\n<\/tr>\n<\/thead>\n
    Name<\/td>\n{“First”:”Terri”,”Middle”:”Lee”,”Last”:”Duffy”}<\/td>\n<\/tr>\n
    PII<\/td>\n{“DOB”:”1971-08-01″,”NatID”:”245797967″}<\/td>\n<\/tr>\n
    LoginID<\/td>\nadventure-works\\terri0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Because the first two values are objects, the entire contents of those objects are returned. However, we can instead return only one of those objects by specify the object name:<\/p>\n
    SELECT [key], value\nFROM OPENJSON(@json, '$.Employees[0].Name');<\/pre>\n
    Now the SELECT<\/strong><\/code> statement returns only the three properties within the Name<\/code><\/strong> object:<\/p>\n\n\n\n\n\n\n\n
    key<\/td>\nvalue<\/td>\n<\/tr>\n<\/thead>\n
    First<\/td>\nTerri<\/td>\n<\/tr>\n
    Middle<\/td>\nLee<\/td>\n<\/tr>\n
    Last<\/td>\nDuffy<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The OPENJSON<\/code><\/strong> examples we’ve looked at so far have used the default schema when returning the data as a rowset, but there are limits to how well we can control the results. Fortunately, the OPENJSON<\/code><\/strong> function also lets us add a WITH<\/code><\/strong> clause to our SELECT<\/strong><\/code> statement in order to define an explicit schema. In the following example, the schema flattens out our data so we can easily see the details for each employee:<\/p>\n
    SELECT *\nFROM OPENJSON(@json, '$.Employees')\nWITH([Name.First] NVARCHAR(25), [Name.Middle] NVARCHAR(25), \n  [Name.Last] NVARCHAR(25), [PII.DOB] DATE, [PII.NatID] INT);<\/pre>\n
    The WITH<\/code><\/strong> clause specifies each column, using names that link to the original JSON. For example, the Name.First<\/code><\/strong> column returns the employee’s first name. The column name is based on the First<\/code><\/strong> property within the Name<\/code><\/strong> object. For each column, we also provide a T-SQL data type. The SELECT<\/strong><\/code> statement now returns the results shown in the following table:<\/p>\n\n\n\n\n\n\n
    Name.First<\/td>\nName.Middle<\/td>\nName.Last<\/td>\nPII.DOB<\/td>\nPII.NatID<\/td>\n<\/tr>\n<\/thead>\n
    Terri<\/td>\nLee<\/td>\nDuffy<\/td>\n1971-08-01<\/td>\n245797967<\/td>\n<\/tr>\n
    Roberto<\/td>\nNULL<\/td>\nTamburello<\/td>\n1974-11-12<\/td>\n509647174<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If we want to define more readable column names, we can instead create column definitions that each includes the new name, followed the data type, and then a path reference, as shown in the following example:<\/p>\n
    SELECT *\nFROM OPENJSON(@json, '$.Employees')\nWITH(FirstName NVARCHAR(25) '$.Name.First', \n  MiddleName NVARCHAR(25) '$.Name.Middle', \n  LastName NVARCHAR(25) '$.Name.Last', \n  BirthDate DATE '$.PII.DOB', \n  NationalID INT '$.PII.NatID');<\/pre>\n
    Notice that, for the path, we do not need to reference the Employees<\/code><\/strong> array itself. That’s taken care of in the OPENJSON<\/code><\/strong> function. But we still need to specify the dollar sign to show the current context. We then follow with the Name<\/code><\/strong> or PII<\/code><\/strong> object name and then the property name. The SELECT<\/strong><\/code> statement now returns the results shown in the following table:<\/p>\n\n\n\n\n\n\n
    FirstName<\/td>\nMiddleName<\/td>\nLastName<\/td>\nBirthDate<\/td>\nNationalID<\/td>\n<\/tr>\n<\/thead>\n
    Terri<\/td>\nLee<\/td>\nDuffy<\/td>\n1971-08-01<\/td>\n245797967<\/td>\n<\/tr>\n
    Roberto<\/td>\nNULL<\/td>\nTamburello<\/td>\n1974-11-12<\/td>\n509647174<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The preceding examples should give you at least a basic idea of how to turn a JSON snippet into rowset data. Again, refer to SQL Server 2016 documentation to get more specifics about how to use the OPENJSON<\/code><\/strong> function.<\/p>\n
    More JSON functions in SQL Server 2016<\/h2>\n
    In addition to OPENJSON<\/code><\/strong>, SQL Server 2016 includes several other functions for working with JSON data. We’ll review how to use the ISJSON<\/code><\/strong>, JSON_value<\/code><\/strong> functions, and JSON_ QUERY<\/code><\/strong> functions.<\/p>\n
    ISJSON<\/h3>\n
    The ISJSON<\/code><\/strong> function lets you test whether a text string is correctly formatted JSON. This is a particularly important function, considering that SQL Server 2016 doesn’t support a JSON data type. At least this way, you have some way to validate your data.<\/p>\n
    The ISJSON<\/code><\/strong> function returns 1<\/code><\/strong> if a string is valid JSON, otherwise returns 0<\/code><\/strong>. The only exception to this is if the string is null, in which case the function returns null. The following SELECT<\/strong><\/code> statement tests our ubiquitous @json<\/code><\/strong> variable to verify whether it is valid:<\/p>\n
    SELECT CASE \n  WHEN ISJSON(@json) > 0 \n    THEN 'The variable value is JSON.' \n    ELSE 'The variable value is not JSON.' \n  END;<\/pre>\n
    As we hoped, the SELECT<\/strong><\/code> statement returns the following results:<\/p>\n
    The variable value is JSON.<\/pre>\n
    Now let’s pass in text that is not valid JSON by tagging on the Age<\/strong> element without a value:<\/p>\n
    DECLARE @json2 NVARCHAR(MAX) = N'\n{\"First\":\"Terri\",\"Middle\":\"Lee\",\"Last\":\"Duffy\",\"Age\"}';\n\nSELECT CASE \n  WHEN ISJSON(@json2) > 0 \n    THEN 'The variable value is JSON.' \n    ELSE 'The variable value is not JSON.' \n  END;<\/pre>\n
    As expected, we receive the second message:<\/p>\n
    The variable value is not JSON.<\/pre>\n
    JSON_VALUE<\/h3>\n
    Another handy JSON-related function in SQL Server 2016 is JSON_VALUE<\/code><\/strong>, which lets us extract a scalar value from a JSON snippet, as shown in the following example:<\/p>\n
    SELECT JSON_VALUE(@json, '$.Employees[0].Name.First');<\/pre>\n
    The JSON_VALUE<\/code><\/strong> function takes two arguments. The first is the JSON itself, and the second is a path that defines which element’s value we want to retrieve. In this case, the path specifies the First<\/code><\/strong> property in the Name<\/code><\/strong> object, which is part of the first element in the Employees<\/code><\/strong> array. As we would expect, the SELECT<\/strong><\/code> statement returns the value Terri<\/code><\/strong>.<\/p>\n
    We can just as easily return the NatID<\/code><\/strong> value for the second employee:<\/p>\n
    SELECT JSON_VALUE(@json, '$.Employees[1].PII.NatID');<\/pre>\n
    Now the SELECT<\/strong><\/code> statement returns 509647174<\/code><\/strong>. Suppose, however, that we try to retrieve something other than a scalar value. For example, the following path specifies only the PII<\/strong> object for the second employee:<\/p>\n
    SELECT JSON_VALUE(@json, '$.Employees[1].PII');<\/pre>\n
    This time, the SELECT<\/strong><\/code> statement returns a null value. By default, the database engine returns a null value if the path does not exist or is not applicable to the current situation. In this example, we’ve specified an element that cannot return a scalar value, so the database engine returns the null value.<\/p>\n
    When specifying a path in a JSON-related expression, you can control the results by preceding the path with the lax<\/strong> or strict<\/strong> option. The lax<\/strong> option is the default and is implied if not specified, which means that the database engine returns a null value if a problem arises. For example, the following path explicitly includes the lax<\/strong> option:<\/p>\n
    SELECT JSON_VALUE(@json, 'lax $.Employees[1].PII');<\/pre>\n
    Once again, out statement returns a null value because we’re specifying an element that cannot return a scalar value. We can instead specify the strict<\/strong> option, in which case, the database engine will raise an error if a problem occurs:<\/p>\n
    SELECT JSON_VALUE(@json, 'strict $.Employees[1].PII');<\/pre>\n
    This time we receive very different results:<\/p>\n
    Property cannot be found in specified path.<\/pre>\n
    JSON_QUERY<\/h3>\n
    Another useful JSON-related tool is the JSON_QUERY<\/code><\/strong> function, which can extract an object or array from a JSON snippet. For example, the following SELECT<\/strong><\/code> statement retrieves the PII<\/code><\/strong> object for the second employee:<\/p>\n
    SELECT JSON_QUERY(@json, 'strict $.Employees[1].PII');<\/pre>\n
    Like the JSON_value<\/code><\/strong> function, the JSON_QUERY<\/code><\/strong> function takes two arguments: the JSON source and a path indicating what data to extract. The SELECT<\/strong><\/code> statement returns the following results:<\/p>\n
    {\"DOB\":\"1974-11-12\",\"NatID\":\"509647174\"}<\/pre>\n
    If we want to return the Employees<\/code><\/strong> array, we simply specify $.Employees<\/code><\/strong> as our path:<\/p>\n
    SELECT JSON_QUERY(@json, 'strict $.Employees');<\/pre>\n
    Now the SELECT<\/strong><\/code> statement returns just about everything in our JSON snippet:<\/p>\n
    [{\"Name\":{\"First\":\"Terri\",\"Middle\":\"Lee\",\"Last\":\"Duffy\"},\"PII\":{\"DOB\":\"1971-08-01\",\"NatID\":\"245797967\"},\"LoginID\":\"adventure-works\\\\terri0\"},{\"Name\":{\"First\":\"Roberto\",\"Middle\":null,\"Last\":\"Tamburello\"},\"PII\":{\"DOB\":\"1974-11-12\",\"NatID\":\"509647174\"},\"LoginID\":\"adventure-works\\\\roberto0\"}]<\/pre>\n
    Summary: JSON and SQL Server 2016<\/h2>\n
    This article should give you what you need to start working with JSON data in SQL Server. As you can see, however, JSON support is nowhere nearly as robust as XML support. And if you’re working with other database management systems, you’ll quickly discover that the JSON features in SQL Server 2016 have some catching up to do before they can match what’s been implemented in other products.<\/p>\n
    Even so, what SQL Server 2016 provides is better than nothing, and the JSON support is solid and could prove more than adequate much of the time. In fact, for some organizations, the JSON features already implemented in SQL Server 2016 will be enough to meet their needs. Best of all, the JSON-related functionality is straightforward and easy-to-use, so you should be able to incorporate it into your workflow with relatively little pain.<\/p>\n<\/div>\n\n\n\n
    \n    
    FAQs<\/h2>\n\n                        
    1. How should I store and query JSON in SQL Server?<\/h3>\n            
    \n                
    Store JSON text in an NVARCHAR(MAX) column (or the native json type in SQL Server 2025), keep it for genuinely semi-structured data rather than as a substitute for columns, and query it with JSON_VALUE for scalars, JSON_QUERY for fragments and OPENJSON to turn it into rows you can join. To index a value inside the JSON, create a computed column with JSON_VALUE and index that. For the full design discussion, see Effective Strategies for Storing and Parsing JSON in SQL Server.<\/p>\n            <\/div>\n                    
    2. What is the difference between FOR JSON AUTO and FOR JSON PATH?<\/h3>\n            
    \n                
    AUTO builds the JSON structure from the query – nesting follows the joins and column names become keys – with little control. PATH lets you define the structure with dot-separated column aliases (for example ‘address.city’), which is what you want when the JSON must match an application’s schema.<\/p>\n            <\/div>\n                    
    3. What is the difference between JSON_VALUE and JSON_QUERY?<\/h3>\n            
    \n                
    JSON_VALUE returns a scalar – a string, number or boolean – at a path. JSON_QUERY returns an object or array as JSON text. Using JSON_VALUE on an object returns NULL, which is the most common cause of unexpected NULLs when reading JSON.<\/p>\n            <\/div>\n                    
    4. How do you convert JSON to rows in SQL Server?<\/h3>\n            
    \n                
    With OPENJSON: OPENJSON(@json) returns key\/value\/type rows, and OPENJSON(@json) WITH (col1 type ‘path’, …) returns a typed rowset you can join, insert or aggregate. The article covers both forms.<\/p>\n            <\/div>\n            <\/section>\n\n\n\n
    <\/p>\n","protected":false},"excerpt":{"rendered":"
    How SQL Server handles JSON: format results with FOR JSON AUTO and PATH, shred JSON into rows with OPENJSON, and read values with JSON_VALUE and JSON_QUERY.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143525],"tags":[4880,4149,4150,4151,6089,4217],"coauthors":[6779],"class_list":["post-2127","post","type-post","status-publish","format-standard","hentry","category-learn","tag-json","tag-learn-sql-server","tag-sql","tag-sql-server","tag-sql-server-2016","tag-xml"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2127","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2127"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2127\/revisions"}],"predecessor-version":[{"id":113152,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2127\/revisions\/113152"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2127"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}