Share via

Formatting T-SQL Queries Output from JSON Model

  • Article

Introduction

Data transmission and processing is very important. In some market segments, how faster is the consultation process, more reliable it is.

A great way to process data and display on a Mobile App or Web Application is using the JSON (JavaScript Object Notation) model.

Let's make a query on a SQL Server Database and provide output of these data in the format used for processing through JSON. This pattern is very useful and can be used in different programming platforms such as: ASP.Net, WinForms and mainly Windows Phone App's.

All records found on a table can be returned in just a single row, with the identification of each column and separated by each record.

Get data output from a query table on SQL Server to only one row, decrease the amount of bytes to move through of the network to the application and this makes the whole process faster at a low cost.

Building the Environment for Testing

So that we can create this data formatting from multiple records in just one SQL Server output row, which is the main advantage of this JSON model, the data carried forward can be done quickly. Let's create a table with some data to our query sample.

See this T-SQL script below

--CREATING A TABLE FOR DEMO
CREATE TABLE dbo.TB_EXAMPLE(
  ID_EXAMPLE int NOT NULL IDENTITY(1,1),
  NM_EXAMPLE varchar(25) NOT NULL,
  CD_ACTIVE bit NOT NULL DEFAULT 1,
);
GO

--INSERTING 100 DIFFERENT ROWS ON TABLE
INSERT INTO TB_EXAMPLE (NM_EXAMPLE) VALUES ('ITEM ' + CONVERT(VARCHAR,ISNULL(@@IDENTITY, 0)));
GO 100

--VIEWING THE 100 ROWS
SELECT * FROM TB_EXAMPLE;
GO

Image 1 - See this output SQL script in the image below

After you create the Demo Table and insert some data, we will focus on just return the information required by user in the JSON format.

Formatting T-SQL outputs to JSON

Facilitating the understanding of the data format used by JSON, we run a common T-SQL query that will return the same query formatted rows.

To show that output data are flexible and can be modified according to user needs, we will limit query to get only first 10 rows, with ID_EXEMPLE >10.

For properly format our data to the JSON pattern, will use the FOR XML PATH method to structure the query result on a single line and the STUFF method to transform the XML result in String data type.

Among these T-SQL settings, also include the ID fields of each row to JSON Object. This can correctly process each row and their custom fields.

So JSON standard brings together each record in curly bracket ("{}") and separate fields by commas. Inside each row, identify the fields and their values ​​separated by colon (":") and each field/value is separated by a comma (",").

E.g.: "Column_Name1":"Column_Value1","Column_Name2":"Column_Value2"

See this T-SQL script below identifying "ID_EXAMPLE" column as "id" and "NM_EXAMPLE" column as "name".

--VIEWING TOP 10 ROWS, WHERE "ID_EXAMPLE" > 10
SELECT TOP 10 ID_EXAMPLE, NM_EXAMPLE FROM TB_EXAMPLE WHERE ID_EXAMPLE > 10;
GO

--RETURNING DATAS ON SINGLE ROW TO "JSON FORMAT" OBJECT
SELECT STUFF((
       SELECT TOP 10 '{id:' + CONVERT(VARCHAR, ID_EXAMPLE) + ',name:"' + CONVERT(VARCHAR, NM_EXAMPLE) + '"},'
FROM TB_EXAMPLE
WHERE ID_EXAMPLE > 10
FOR XML PATH('')), 1, 0, '') AS A;
GO

Image 2 - See this output SQL script in the image below (click to enlarge)

So, we formatted a simple and convenient data output from a SQL Server Table for the JSON pattern. It's important to create a simple structure to format these data types, then formatted query becomes faster and the maintenance of queries by user on the Application is also easier to data identified.

To validate if the data output in this statement are correct, you can compare one or more rows with the simple query. In this example (Image 2), compare the data with "ID_EXAMPLE" = 12.

We can see, the values ​​are correct if comparing the values ​​in the simple query  "ID_EXAMPLE" = 12 and "NM_EXAMPLE" = "ITEM 11" and the value in the format query on JSON standard. See: "{id:12,name:"ITEM 11"}".

Conclusion

Allow your users to access their information on different platforms is an advantage and a great way to valorize your Application, but It's extremely important get faster output data on query. This allows your users aren't dependent on Web link.

Process and format the data directly in SQL Server becomes a great advantage to reduce processing on your server business, where your applications are hosted on the machine or even used by the end user layer.

The pattern defined by JSON objects distributed processing between the layers of solution, so it makes it more lean and easy data portability implementation.

One more time the T-SQL demonstrated how to make life easier for a Developer to share information from your SQL Server Database.

Update - SQL Server 2016

SQL Server 2016 brings new features related to JSON, which include the option to export data from SQL Server as JSON, or format query results as JSON, by adding the FOR JSON clause to a SELECT statement. You can read more in this link.

References

See Also

Other Languages