NEW T-SQL FEATURES IN SQL SERVER 2016 – XV | JSON_VALUE()

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | JSON_VALUE()

New feature – XV | JSON_VALUE()


This function is used to extracts a scalar value from a JSON string.

Syntax

--

JSON_VALUE ( expression , path )

--

Notes

1. If JSON_VALUE finds JSON that is not valid in expression before it finds the value identified by path, the function returns an error.

2. If *JSON_VALUE doesn’t find the value identified by path, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in expression.

3. This function returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression.

4. If the value is greater than 4000 characters:
In lax mode, JSON_VALUE returns null.
In strict mode, JSON_VALUE returns an error.

5. If you have to return scalar values greater than 4000 characters, use OPENJSON instead of JSON_VALUE. Refer below url for OPENJSON function- https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xii-openjson-function-sql-server/

/* First Example JSON_VALUE */

--

DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }' 
 
 SELECT 
  JSON_VALUE(@jsonInfo,'$.info.address.town') Addresstown
 ,JSON_VALUE(@jsonInfo,'$.info.address.county') Addresstown
 ,JSON_VALUE(@jsonInfo,'$.info.address.country') Addresstown
GO

OUTPUT

Addresstown              Addresstown       Addresstown
------------------------ ----------------------------------
Bristol                  Avon              England

(1 row affected)

--

/* Second Example JSON_VALUE */

--

DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }' 
SELECT JSON_VALUE(@jsonInfo,'$.info.tags[0]') AS [SportsTag1]
	  ,JSON_VALUE(@jsonInfo,'$.info.tags[1]') AS [SportsTag2]

SportsTag1               SportsTag2
------------------------ ------------------
Sport                    Water polo

(1 row affected)


--

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com