Convert SQL Server Data to JSON

Introduction

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;

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;

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

Summary

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