Edit

Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)

  • Article

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

The built-in support for JSON includes the following built-in functions described briefly in this topic.

  • ISJSON tests whether a string contains valid JSON.

  • JSON_VALUE extracts a scalar value from a JSON string.

  • JSON_QUERY extracts an object or an array from a JSON string.

  • JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string.

JSON text for the examples on this page

The examples on this page use the JSON text similar to the content shown in the following example:

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

This JSON document, which contains nested complex elements, is stored in the following sample table:

CREATE TABLE Families (
   id int identity constraint PK_JSON_ID primary key,
   doc nvarchar(max)
)

Validate JSON text by using the ISJSON function

The ISJSON function tests whether a string contains valid JSON.

The following example returns rows in which the JSON column contains valid JSON text. Note that without explicit JSON constraint, you can enter any text in the NVARCHAR column:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0

For more info, see ISJSON (Transact-SQL).

Extract a value from JSON text by using the JSON_VALUE function

The JSON_VALUE function extracts a scalar value from a JSON string. The following query will return the documents where the id JSON field matches the value AndersenFamily, ordered by city and state JSON fields:

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

The results of this query are shown in the following table:

Name City County
AndersenFamily NY Manhattan

For more info, see JSON_VALUE (Transact-SQL).

Extract an object or an array from JSON text by using the JSON_QUERY function

The JSON_QUERY function extracts an object or an array from a JSON string. The following example shows how to return a JSON fragment in query results.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
       JSON_QUERY(f.doc, '$.parents') AS Parents,
       JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'

The results of this query are shown in the following table:

Address Parents Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" } [{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ] { "familyName": "Wakefield", "givenName": "Robin" }

For more info, see JSON_QUERY (Transact-SQL).

Parse nested JSON collections

OPENJSON function enables you to transform JSON sub-array into the rowset and then join it with the parent element. As an example, you can return all family documents, and "join" them with their children objects that are stored as an inner JSON array:

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       c.givenName, c.grade
FROM Families f
		CROSS APPLY OPENJSON(f.doc, '$.children')
			WITH(grade int, givenName nvarchar(100))  c

The results of this query are shown in the following table:

Name City givenName grade
AndersenFamily NY Jesse 1
AndersenFamily NY Lisa 8

We are getting two rows as a result because one parent row is joined with two child rows produced by parsing two elements of the children subarray. OPENJSON function parses children fragment from the doc column and returns grade and givenName from each element as a set of rows. This rowset can be joined with the parent document.

Query nested hierarchical JSON sub-arrays

You can apply multiple CROSS APPLY OPENJSON calls in order to query nested JSON structures. The JSON document used in this example has a nested array called children, where each child has nested array of pets. The following query will parse children from each document, return each array object as row, and then parse pets array:

SELECT	familyName,
	c.givenName AS childGivenName,
	c.firstName AS childFirstName,
	p.givenName AS petName 
FROM Families f 
	CROSS APPLY OPENJSON(f.doc) 
		WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
		CROSS APPLY OPENJSON(children) 
		WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
			OUTER APPLY OPENJSON (pets)
			WITH (givenName nvarchar(100))  as p

The first OPENJSON call will return fragment of children array using AS JSON clause. This array fragment will be provided to the second OPENJSON function that will return givenName, firstName of each child, as well as the array of pets. The array of pets will be provided to the third OPENJSON function that will return the givenName of the pet. The results of this query are shown in the following table:

familyName childGivenName childFirstName petName
AndersenFamily Jesse Merriam Goofy
AndersenFamily Jesse Merriam Shadow
AndersenFamily Lisa Miller NULL

The root document is joined with two children rows returned by first OPENJSON(children) call making two rows (or tuples). Then each row is joined with the new rows generated by OPENJSON(pets) using OUTER APPLY operator. Jesse has two pets, so (AndersenFamily, Jesse, Merriam) is joined with two rows generated for Goofy and Shadow. Lisa doesn't have the pets, so there are no rows returned by OPENJSON(pets) for this tuple. However, since we are using OUTER APPLY we are getting NULL in the column. If we put CROSS APPLY instead of OUTER APPLY, Lisa would not be returned in the result because there are no pets rows that could be joined with this tuple.

Compare JSON_VALUE and JSON_QUERY

The key difference between JSON_VALUE and JSON_QUERY is that JSON_VALUE returns a scalar value, while JSON_QUERY returns an object or an array.

Consider the following sample JSON text.

{
	"a": "[1,2]",
	"b": [1, 2],
	"c": "hi"
}

In this sample JSON text, data members "a" and "c" are string values, while data member "b" is an array. JSON_VALUE and JSON_QUERY return the following results:

Path JSON_VALUE returns JSON_QUERY returns
$ NULL or error { "a": "[1,2]", "b": [1,2], "c":"hi"}
$.a [1,2] NULL or error
$.b NULL or error [1,2]
$.b[0] 1 NULL or error
$.c hi NULL or error

Test JSON_VALUE and JSON_QUERY with the AdventureWorks sample database

Test the built-in functions described in this topic by running the following examples with the AdventureWorks sample database. For info about where to get AdventureWorks, and about how to add JSON data for testing by running a script, see Test drive built-in JSON support.

In the following examples, the Info column in the SalesOrder_json table contains JSON text.

Example 1 - Return both standard columns and JSON data

The following query returns values from both standard relational columns and from a JSON column.

SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
 JSON_QUERY(Info,'$.ShippingInfo') ShippingInfo,
 JSON_QUERY(Info,'$.BillingInfo') BillingInfo,
 JSON_VALUE(Info,'$.SalesPerson.Name') SalesPerson,
 JSON_VALUE(Info,'$.ShippingInfo.City') City,
 JSON_VALUE(Info,'$.Customer.Name') Customer,
 JSON_QUERY(OrderItems,'$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0

Example 2- Aggregate and filter JSON values

The following query aggregates subtotals by customer name (stored in JSON) and status (stored in an ordinary column). Then it filters the results by city (stored in JSON) and OrderDate (stored in an ordinary column).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid=3;

SET @city=N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID=@territoryid
 AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
 AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
HAVING SUM(SubTotal)>1000

Update property values in JSON text by using the JSON_MODIFY function

The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.

The following example updates the value of a JSON property in a variable that contains JSON.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London')

For more info, see JSON_MODIFY (Transact-SQL).

Learn more about JSON in SQL Server and Azure SQL Database

Microsoft videos

Note

Some of the video links in this section may not work at this time. Microsoft is migrating content formerly on Channel 9 to a new platform. We will update the links as the videos are migrated to the new platform.

See Also

ISJSON (Transact-SQL)
JSON_VALUE (Transact-SQL)
JSON_QUERY (Transact-SQL)
JSON_MODIFY (Transact-SQL)
JSON Path Expressions (SQL Server)