Azure Data Studio- Export Any SQL SERVER Query As JSON

In the industry, I see that Azure Data Studio (ADS) is getting adopted along with SQL Server Management Studio (SSMS). I will still say that ADS is not the replacement of SSMS and for the near future it seems that they will co-exist. Recently, one of my clients’ Comprehensive Database Performance Health Check asked me if I know a way to export any query as JSON with SSMS without writing code.

Honestly, it is not possible to generate from the query output to JSON without writing code in SQL Server Management Studio but it is possible to do that in the Azure Data Studio.

Azure Data Studio – Query as JSON

If you are new to Azure Data Studio, here are the blog posts which you can refer to

• SQL SERVER – Getting Started with Azure Data Studio
• SQL SERVER – Difference Between Azure Data Studio and SQL Server Management Studio

Now let us see how quickly we can generate JSON from Azure Data Studio.

First, create a connection to the database by clicking on the new connection in ADS. Next, open the new query window and type the following query for the sample database WideWorldImporters.

SELECT TOP 100 *
FROM WideWorldImporters.Sales.Invoices

Now run the query and it will bring the following output in the ADS.

If you look at the very right side of the ADS there are five different icons listed there. The third icon is the one that is important for our task to convert the query results to JSON. Click on it and it will immediately open a new window with the output in the JSON for the query result.

Additionally, you can also easily navigate in your JSON document based on the top dropdown.

As JSON numbers start with the zero. When you select the document 2 and it will select the third record (document) in your JSON collection.

I recently worked with MongoDB for a client and worked with JSON documents. I really fell like how JSON works and it indeed is helpful to learn about MongoDB and JSON.

Here are some of the relevant blog posts on MongoDB.

• Foundations of Document Databases with MongoDB – Video Course
• SQL Terms vs MongoDB Terms
• MongoDB Compass – Missing a Schema Section
• MongoDB Fundamentals – Getting Started – Day 1 of 6
• MongoDB Fundamentals – CRUD: Creating Objects – Day 2 of 6
• MongoDB Fundamentals – CRUD: Reading Objects – Day 3 of 6
• MongoDB Fundamentals – CRUD: Updating Objects – Day 4 of 6
• MongoDB Fundamentals – CRUD: Deleting Objects – Day 5 of 6
• MongoDB Fundamentals – Mapping Relational SQL – Day 6 of 6

Reference: Pinal Dave (https://blog.sqlauthority.com)