Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 97 of 212

85 C H A P T E R 5 | Broader data access Example 5-21: Using the ISJSON function SELECT ProductID, Name, ProductNumber, Reviews FROM Production.Product WHERE ISJSON(Reviews) > 0; JSON_VALUE Extracts a scalar value with an nvarchar(4000) data type from a JSON string. This function takes two arguments, as shown in Example 5-22. The first argument is either the name of a variable to which you have assigned a JSON string or the name of a column containing JSON. The second argument is the path of the property to extract. If the JSON is invalid or the second argument is an object and not a property, the function returns an error. It also returns an error if the result is larger than 4,000 characters in strict mode. Example 5-22: Using the JSON_VALUE function SELECT ProductID, Name, ProductNumber, JSON_VALUE(Reviews, '$.Reviewer.Email') ReviewerEmail FROM Production.Product WHERE Reviews IS NOT NULL Note If it is possible for a value to be greater than 4,000 characters, use the OPENJSON function instead. JSON_QUERY Extracts an object or array with an nvarchar(max) data type from a JSON string, as shown in Example 5-23. As with the JSON_VALUE function, the JSON_QUERY's first argument is a variable or column containing JSON, and the second argument is the property path. This function returns an error if the JSON is invalid or the property value is not an object. In this example, using '$.Reviewer.Name' as the second argument results in an error because the value is scalar, whereas '$.Reviewer' is an array containing the Name and Email properties and therefore valid as an argument to the JSON_QUERY function. Example 5-23: Using the JSON_QUERY function DECLARE @json NVARCHAR(1000); SET @json = N'{"ReviewID":1, "Reviewer":{"Name":"John Smith", "Email":"john@fourthcoffee.com"}, "Rating":5, "ReviewDate":"2013-09-18T00:00:00"}'; SELECT JSON_QUERY(@json, '$.Reviewer') Reviewer; JSON_MODIFY Updates a property value in a JSON string, as shown in Example 5-24, and returns the updated string. Its first argument is the JSON string or reference, its second argument is the property path, and its third argument is the new property value. With this function, you can modify only one property at a time, but you can nest function calls if you need to make multiple changes as shown in the example.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016