<< SQL Server Hosting - HostForLIFE :: SSIS Expression - Convert Date To String | SQL Server Hosting - HostForLIFE :: DDL, DQL, DML, DCL, TCL with Examples >>

SQL Server Hosting - HostForLIFE :: Convert SQL Server Data to JSON

March 9, 2022 07:31 by author

Peter

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

You can convert a SQL query results in JSON format in SQL Server by simply adding FOR JASON clause to the query. FOR JASON is used with PATH and AUTO
SELECT name, surname
FROM emp
FOR JSON AUTO;

Here is a simple SQL query on Northwind database that 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;

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"}]

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 by 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;

Here is a detailed article on JSON in SQL Server with various options.