SQL 2019 Hosting Server Tips: Convert SQL Server Data to JSON

JSON format has become a standard way to represent data objects into strings. JSON format is commonly used in APIs to transfer data from one application to other via APIs. In this article, let’s learn how to convert SQL Server data to JSON format.

You can convert SQL query results in JSON format in SQL Server by adding the FOR JASON clause to the query. FOR JASON is used with PATH and AUTO 

SELECT name, surname
FROM emp
FOR JSON AUTO;

SQL

A simple SQL query on the Northwind database returns 10 orders from the Orders table. 

SELECT TOP (10) [OrderID]
      ,[OrderDate]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipPostalCode]
      ,[ShipCountry]
  FROM [Northwind].[dbo].[Orders]

The output in SSMS looks like this.

 

Now, let’s add FOR JASON PATH clause at the end of the SQL query.

SELECT TOP (10) [OrderID]
      ,[OrderDate]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipPostalCode]
      ,[ShipCountry]
  FROM [Northwind].[dbo].[Orders]
  FOR JSON PATH;

SQL

The new output looks like this -- that is a JSON object. 

[
  {
    "OrderID": 10248,
    "OrderDate": "1996-07-04T00:00:00",
    "ShipName": "Vins et alcools Chevalier",
    "ShipAddress": "59 rue de l'Abbaye",
    "ShipCity": "Reims",
    "ShipPostalCode": "51100",
    "ShipCountry": "France"
  },
  {
    "OrderID": 10249,
    "OrderDate": "1996-07-05T00:00:00",
    "ShipName": "Toms Spezialitäten",
    "ShipAddress": "Luisenstr. 48",
    "ShipCity": "Münster",
    "ShipPostalCode": "44087",
    "ShipCountry": "Germany"
  },
  {
    "OrderID": 10250,
    "OrderDate": "1996-07-08T00:00:00",
    "ShipName": "Hanari Carnes",
    "ShipAddress": "Rua do Paço, 67",
    "ShipCity": "Rio de Janeiro",
    "ShipPostalCode": "05454-876",
    "ShipCountry": "Brazil"
  },
  {
    "OrderID": 10251,
    "OrderDate": "1996-07-08T00:00:00",
    "ShipName": "Victuailles en stock",
    "ShipAddress": "2, rue du Commerce",
    "ShipCity": "Lyon",
    "ShipPostalCode": "69004",
    "ShipCountry": "France"
  },
  {
    "OrderID": 10252,
    "OrderDate": "1996-07-09T00:00:00",
    "ShipName": "Suprêmes délices",
    "ShipAddress": "Boulevard Tirou, 255",
    "ShipCity": "Charleroi",
    "ShipPostalCode": "B-6000",
    "ShipCountry": "Belgium"
  },
  {
    "OrderID": 10253,
    "OrderDate": "1996-07-10T00:00:00",
    "ShipName": "Hanari Carnes",
    "ShipAddress": "Rua do Paço, 67",
    "ShipCity": "Rio de Janeiro",
    "ShipPostalCode": "05454-876",
    "ShipCountry": "Brazil"
  },
  {
    "OrderID": 10254,
    "OrderDate": "1996-07-11T00:00:00",
    "ShipName": "Chop-suey Chinese",
    "ShipAddress": "Hauptstr. 31",
    "ShipCity": "Bern",
    "ShipPostalCode": "3012",
    "ShipCountry": "Switzerland"
  },
  {
    "OrderID": 10255,
    "OrderDate": "1996-07-12T00:00:00",
    "ShipName": "Richter Supermarkt",
    "ShipAddress": "Starenweg 5",
    "ShipCity": "Genève",
    "ShipPostalCode": "1204",
    "ShipCountry": "Switzerland"
  },
  {
    "OrderID": 10256,
    "OrderDate": "1996-07-15T00:00:00",
    "ShipName": "Wellington Importadora",
    "ShipAddress": "Rua do Mercado, 12",
    "ShipCity": "Resende",
    "ShipPostalCode": "08737-363",
    "ShipCountry": "Brazil"
  },
  {
    "OrderID": 10257,
    "OrderDate": "1996-07-16T00:00:00",
    "ShipName": "HILARION-Abastos",
    "ShipAddress": "Carrera 22 con Ave. Carlos Soublette #8-35",
    "ShipCity": "San Cristóbal",
    "ShipPostalCode": "5022",
    "ShipCountry": "Venezuela"
  }
]

YAML

Now, you can use this same return value from SQL query in your application to read JSON objects in your code. 

Using the same method, you can convert a SQL Server Table to JSON using a SELECT * or SELECT column names query on the entire table. The following SQL query converts all rows of a SQL Server table to a JSON string. 

SELECT [OrderID]
      ,[OrderDate]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipPostalCode]
      ,[ShipCountry]
  FROM [Northwind].[dbo].[Orders]
  FOR JSON PATH;

Best SQL 2019 Hosting Recommendation

One of the most important things when choosing a good SQL 2019 hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable SQL 2019, their servers are optimized for PHP web applications. The performance and the uptime of the hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. 

At HostForLIFEASP.NET, customers can also experience fast SQL 2019 hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for SQL 2019. And the engineers do regular maintenance and monitoring works to assure its Orchard hosting are security and always up.