Netherlands: Software

Introductie van Micorosoft SQL Server 2016

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

Contents of this Issue

Navigation

Page 94 of 212

82 C H A P T E R 5 | Broader data access When you use the OPENJSON function, you decide whether to call it with an explicit schema or a default schema. With an explicit schema, you define the structure of the table that you want OPENJSON to return. With a default schema, the function returns a key column containing property names, a value column containing the values for each property, and type column containing a value for the JSON data type, as shown in the following table: Type column value JSON data type 0 null 1 string 2 int 3 true/false 4 array 5 object When you call the OPENJSON function with a default schema, as shown in Example 5-17, you pass in the JSON text (or a variable to which you assign JSON text) as an argument and omit the WITH clause. The result of this function is a table containing one row for each property of the object. This table contains three columns—Key, Value, and Type—as shown in Figure 5-5. Example 5-17: Using the OPENJSON function to convert a single JSON object to a table SELECT * FROM OPENJSON('{"Reviewer":{"Name":"John Smith", "Email":"john@fourthcoffee.com"},"Rating":5,"ReviewDate":"2013-09-18T00:00:00"}') Figure 5-5: Rows returned by the OPENJSON function with a single JSON object as its argument. Note Your database compatibility level must be set to 130 to use the OPENJSON function. All other JSON functions work with lower compatibility levels. You can also pass an array of JSON objects to the OPENJSON function, as shown in Example 5-18. When you do this, the resulting table contains one row for each element of the array, as shown in Figure 5-6. Example 5-18: Using the OPENJSON function to convert a JSON object array to a table SELECT * FROM OPENJSON( '[ {"Reviewer":{"Name":"John Smith","E-mail":"john@fourthcoffee.com"}, "Rating":3,"ReviewDate":"2016-01-20T00:00:00"}, {"Reviewer":{"Name":"Jill","E-mail":"jill@margiestravel.com"}, "Rating":4,"ReviewDate":"2016-02-10T00:00:00"} ]')

Articles in this issue

Archives of this issue

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