This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 came with varieties of JSON options. We had a requirement like select table data into JSON format and also combine this result with other table’s JSON.
When you are trying to get JSON formatted data using FOR JSON PATH, it adds [square brackets] in a JSON string which may create a problem, while you are combining the many JSONs.
You can use WITHOUT_ARRAY_WRAPPER option to remove this additional [square brackets].
Below is a full demonstration of this:
Create a table with sample records:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE tbl_Students ( Rno INT PRIMARY KEY ,STudName VARCHAR(20) ,ClassName CHAR(1) ) GO INSERT INTO tbl_Students VALUES (1,'Anvesh','A'),(2,'Neevan','B'),(3,'Toby','C') ,(5,'Roy','A'),(4,'Jenny','B'),(6,'Kaviy','C') ,(7,'Martin','A'),(8,'Laxmi','B'),(9,'Nion','C') GO |
Prepare JSON using FOR JSON PATH:
You can see additional [ ] in the result string.
|
1 2 3 4 5 6 7 8 |
SELECT TOP 2 * FROM tbl_Students ORDER BY Rno FOR JSON PATH GO --Result: --[{"Rno":1,"STudName":"Anvesh","ClassName":"A"},{"Rno":2,"STudName":"Neevan","ClassName":"B"}] |
Now, you can find result string without [ ].
|
1 2 3 4 5 6 7 8 |
SELECT TOP 2 * FROM tbl_Students ORDER BY Rno FOR JSON PATH, WITHOUT_ARRAY_WRAPPER GO --Result: --{"Rno":1,"STudName":"Anvesh","ClassName":"A"},{"Rno":2,"STudName":"Neevan","ClassName":"B"} |
Leave a Reply