Skip to content

validate-query-and-change-json-data-with-built-in-functions-sql-server.md

Latest commit

 

History

History
192 lines (143 loc) · 7.72 KB

validate-query-and-change-json-data-with-built-in-functions-sql-server.md

File metadata and controls

192 lines (143 loc) · 7.72 KB

title: "Validate, Query, and Change JSON Data with Built-in Functions (SQL Server) | Microsoft Docs" ms.custom: "" ms.date: "07/17/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database" ms.component: "json" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "dbe-json" ms.tgt_pltfrm: "" ms.topic: conceptual helpviewer_keywords:
  • "JSON, built-in functions"
  • "functions (JSON)" ms.assetid: 6b6c7673-d818-4fa9-8708-b4ed79cb1b41 caps.latest.revision: 21 author: "jovanpop-msft" ms.author: "jovanpop" ms.reviewer: douglasl manager: craigg monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"

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

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]

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 following JSON text, which contains a complex element.

DECLARE @jsonInfo NVARCHAR(MAX)

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

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 column json_col contains valid JSON.

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col) > 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 example extracts the value of the nested JSON property town into a local variable.

SET @town = JSON_VALUE(@jsonInfo, '$.info.address.town')

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 FirstName, LastName, JSON_QUERY(jsonInfo,'$.info.address') AS Address
FROM Person.Person
ORDER BY LastName

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

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 blog posts

For specific solutions, use cases, and recommendations, see these blog posts about the built-in JSON support in SQL Server and Azure SQL Database.

Microsoft videos

For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

See Also

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