Keep talking about the JSON function in 2016 (1)

First to test the data, the data is used before,

Don't mind the details.

borrow the test data from the previous articleCreate TableT1 (IDint Identity, namenvarchar( -), Chineseint, Mathint)Insert  intoT1Values('Zhang San', -, the),('John Doe', the, -),('Harry', the, -),('Zhao Liu', -,NULL);

Then we use the JSON generated in this table to test, the simplest example

Declare @v nvarchar( -)=N'[{"id": 1, "name": "Zhang San", "Chinese": "$", "math": 80},{"id": 2, "name": "John Doe", "Chinese": +, "math": 90},{"id": 3, "name": " Harry "," Chinese ": Chinese," Math ": 100},{" ID ": 4," name ":" Zhao Liu "," ": []'Select *  fromOpenjson (@v) with(IDint '$.id', namenvarchar( -)'$.name', Mathint '$. Math', Chineseint '$. Chinese')------------------------ID name Math Chinese----------- -------------------------------------------------- ----------- -----------1Tom the           -2John doe -           the3Harry -          the4Zhao LiuNULL         -

It's the simplest answer. Parses the JSON into the form of a result set, or null if the node does not exist

Actually, it's similar to OpenXML's usage.

Then use 2, you can parse out the format of the JSON format, when the Openjson is not added after the keyword with, it is considered to parse out the structure of the JSON itself. Let's say (because it's a bit strange to use formatting to text underneath this statement, so just stick it with the result set, it's a bit messy, everyone will)

Declare @v nvarchar( -)=N'{"ID": 1, "name": "Zhang San", "Chinese": +, "Math" :'Select *  fromOpenjson (@v)Keyvalue TypeID1    2name Zhang San1Chinese -    2Math the    2

The first 2 are all well understood, the third position, the type. Type, in Openjson's parsing.

0 NULL

1 string

2 int

3 bool

4 array

5 Object

So it's not surprising that the value of just the type is displayed, Id,math, Chinese are all shaped, the name is a string, and then an example

Declare @v nvarchar( -)=N'{"ID": True, "name": null, "Chinese": 9.9, "Math": "Hello", "arr": [1,2,3,4], "obj": {"name": "Test"}}'Select *  fromOpenjson (@v)Keyvalue TypeID true3nameNULL    0Chinese9.9    2Math Hello1arr[1,2,3,4]    4obj {"name": "Test"}5  

As you can see here, even 9.9 of these types, using Openjson will also be considered plastic, so even if you can parse out the type, but also do not believe that it can bring you accurate data type is right.
Some people may ask, if it is a JSON containing multiple rows of objects, in fact, can be used in Openjson with the format to parse, then how does it determine? For example

Declare @v nvarchar (*= N'[{"id": 1, "name": "Zhang San", "Chinese": $, "Math": 80},{"id": 2, "name": "John Doe", " Chinese ":", "math": 90},{"id": 3, "name": "Harry", "Chinese":, "math": 100},{"id": 4, "name": "Zhao Liu", "Chinese": [] '

Using Openjson to parse directly, it is clear that 4 columns are of type object, and that is all.

In general, the use of this is the part of ~ AH ~ this time to here, the next chapter to write

Keep talking about the JSON function in 2016 (1)