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

This article introduces you how to directly convert the database SQL query results into JSON, the content is very detailed, interested friends can refer to, hope to be helpful to you.

JSON is a lightweight data exchange format that stores and represents data in a text format that is completely independent of the programming language. The concise and clear hierarchical structure makes JSON an ideal data exchange language. It is easy for people to read and write, at the same time, it is also easy for machine analysis and generation, and effectively improve the efficiency of network transmission.

SQL and Json

SQLServer has added support for Json data since 2016, and we can easily convert the queried data into Json format through SQL statements, which provides a good way for us to store and interact data.

Today we'll talk about how to generate Json text for query results in SQLServer.

Test data preparation

In order to test the results, let's first prepare the test data, here is a simple sales table:

Declare @ sale table (FName nvarchar (50), FDistrict nvarchar (50), FAmount decimal (28) Insert into @ sale values ('Zhang San', 'Beijing', 20000), ('Zhang San', 'Shanghai', 50000), ('Zhang San', 'Shenzhen', 40000), ('Zhang San', 'Guangzhou', 30000), ('Li Si', 'Beijing', 30000), ('Li Si', 'Shanghai', 50000), ('Li Si', 'Shenzhen', 40000), ('Li Si', 'Guangzhou')

Demo data

Magical for json auto clause

Add for json auto to the from clause of the query, and the query results are automatically converted into json text, which is the simplest way of transformation.

Select * from @ sale for json auto

The result of the query is the json text.

Query results are automatically converted to Json

Grab the Json, and the effect is as follows:

Automatically exported Json text

Does it feel amazing? if you think SQLServer's support for exporting Json is just that, then you underestimate Microsoft. Let's talk about the more powerful export features.

Magical for json path clause

Having seen the magic for json auto clause, let's take a look at another magic for json path clause that uses column aliases or column names to determine the key name in the JSON output.

For example, we take the "name" as a top-level node and set the region and sales as a superior node "sales situation":

Select FName as name, FDistrict as' sales situation. Regional', FAmount as' sales. Amount 'from @ sale for json path

The result of the query is also Json text:

Query results are automatically converted to Json

Grab the Json, and the effect is as follows:

Automatically exported Json text

Do you notice any difference? "sales situation." area "," sales situation. "the period in the amount automatically takes the" sales situation "as the superior node, and the" area "and" amount "as the child nodes.

Magical ROOT option

Friends who are familiar with Json will find that the Json exported by for json auto and for json path is directly an array, and each record is an array element without a root node. What should we do if we want to add a root node to Json?

SQLServer provides the Root option to add the root node:

Select FName as name, FDistrict as' sales situation. Regional', FAmount as' sales. Amount 'from @ sale for json path, root (' Business Information')

The result of the query is still Json text:

Query results are automatically converted to Json

Grab the Json, and the effect is as follows:

Automatically exported Json text

You can see that Json adds the root node "business information".

On how to directly convert the database SQL query results to JSON to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.