Return SQL Server 2016 JSON format In 1 row format

The newest version of Microsoft SQL Server, which it version 2016, comes with JSON native support. So you don't need to convert anymore at script side with 3rd party module like Newtonsoft library.


You can directly add command FOR JSON PATH/AUTO at the end of your SQL Syntax and the result will displayed in JSON format. For example:

SELECT employee_id, employee_firstname, employee_lastname
    FROM employee WHERE employee_lastname LIKE '%SMITH%' FOR JSON PATH

But, one thing that even myself not sure is this bug from Microsoft or not, is if your SQL syntax return the long JSON result, it will be truncated automatically, which it at the end will be identified as the un-completed JSON string or non valid JSON string. Or even the result will be divided into more than 1 row, so you need to loop all the data to combine into a complete JSON string format.

In the SQL syntax example that I mentioned above, for example will have 500 rows of data returned. I am pretty sure that the result will truncated at some point of wording.

To solve this, you need to set and store the SQL syntax into a variable, then do the SELECT statement against these variable.

This is the trick

-- we don't know how big or how long the result of your SELECT statement 

DECLARE @json AS NVARCHAR(MAX) 

SET @json = 
    (SELECT employee_id, employee_firstname, employee_lastname

        FROM employee WHERE employee_lastname LIKE '%SMITH%' FOR JSON PATH)

IF @json IS NULL

   -- if no result returned, then bla bla bla
ELSE
   SELECT @json

Then the result will be returned in 1 complete row JSON format.