Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use the json function in sql server2016". In daily operation, I believe many people have doubts about how to use the json function in sql server2016. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use the json function in sql server2016". Next, please follow the editor to study!

To test the basics, construct a json format from the query results

Create table T1 (ID int identity,name nvarchar (50), Chinese int, Math int) insert into T1 values ('Zhang San', 90 ~ 80), ('Li Si', 75 ~ 90), ('Wang Wu' 68100) select * from t1select * from T1 for json auto-- query result ID name Chinese Math-- 1 Zhang San 90 802 Li Si 75 903 Wang Wu 68 100--json format [{"ID": 1 "name": "Zhang San", "Chinese": 90, "Math": 80}, {"ID": 2, "name": "Li Si", "Chinese": 75, "Math": 90}, {"ID": 3, "name": "Wang Wu", "Chinese": 68, "Math": 100}]

This is the result of the query using json in the default mode. Is it very clear?

Then we keep up our efforts, and the second wave is like this. If we want to continue to have a hierarchical relationship. We can still write like this. For example, putting the scores in a node called points can also be layered.

Select ID, name, Chinese as [Points.Chinese], Math as [Points.Math] from T1 for json path-- results json [{"ID": 1, "name": "Zhang San", "Points": {"Chinese": 90, "Math": 80}}, {"ID": 2, "name": "Li Si", "Points": {"Chinese": 75, "Math": 90}, {"ID": 3, "name": "Wang Wu" "Points": {"Chinese": 68, "Math": 100}}]

Their scores are put in json and wrapped in a point.

If I were to add a header to this result, of course, I could use an alias [root.col] for each column, but it would be a bit verbose. So we can use this root keyword to add a top node

Select ID, name, Chinese as [Points.Chinese], Math as [Points.Math] from T1 for json path,root ('root')-returned json result {"root": [{"ID": 1, "name": "Zhang San", "Points": {"Chinese": 90, "Math": 80}}, {"ID": 2, "name": "Li Si", "Points": {"Chinese": 75, "Math": 90}} {"ID": 3, "name": "Wang Wu", "Points": {"Chinese": 68, "Math": 100}}]}

Of course, query, input data will inevitably encounter null values, in this regard, how does for json deal with it? I added a piece of data to the test table to query.

The returned results of insert into T1 values ('Zhao Liu', 100 ID null) select ID, name, Chinese as [Points.Chinese], Math as [Points.Math] from T1 where id in (3,4) for json auto--json [{"ID": 3, "name": "Wang Wu", "Points.Chinese": 68, "Points.Math": 100}, {"ID": 4, "name": "Zhao Liu", "Points.Chinese": 100})

In auto mode, if the value is null, the attribute is ignored. In this way, it is easy to return a different number of properties for each collection, which is not pretty. So in this case, we can use the incluede_null_values keyword, even if it is null, to bring it out.

Select ID, name, Chinese as [Points.Chinese], Math as [Points.Math] from T1 where id in (3,4) for json auto, the returned result of include_null_values--json [{"ID": 3, "name": "Wang Wu", "Points.Chinese": 68, "Points.Math": 100}, {"ID": 4, "name": "Zhao Liu", "Points.Chinese": 100, "Points.Math": null})]

Using this keyword, you can bring out the null value, where the value is the Null value

At this point, the study on "how to use the json function in sql server2016" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.