Extra parenthesis '{' is coming in JSON output from SQL server-CodePudding

I am new in JSON & want to generate JSON output directly from SQL server.

But while calling SP from C#.net, i am getting an extra '{' bracket in the start. And while trying to store the JSON into SQL, it is giving error saying it is not valid JSON.

Below is my code from Stored procedure.

Create PROCEDURE [dbo].[GetData]
As 
BEGIN

SELECT Trucknumber,
        (
        select * from TruckDetails
        where TruckId = Truckmaster.TruckId
        FOR JSON AUTO
        ) AS CompData  
    FROM Truckmaster
Where TruckNumber = 'HP09A3487' 
FOR JSON AUTO

END

C# Code------------------------------------

public StringBuilder RunStoredProcedureWithString(string strSPName)
        {
            StringBuilder jsonResult = new StringBuilder();

            var queryWithForJson = "exec "   strSPName;
            var conn = new SqlConnection(GenerateConnectionString(_DBName));
            var cmdNew = new SqlCommand(queryWithForJson, conn);
            conn.Open();
            var reader = cmdNew.ExecuteReader();
            if (!reader.HasRows)
            {
                jsonResult.Append("[]");
            }
            else
            {
                while (reader.Read())
                {
                   jsonResult.Append(reader.GetValue(0).ToString());
                }
            }
            return jsonResult;
        }

Calling Stored procedure.

 var strJson = objDBSupport.RunStoredProcedureWithString("GetData");
 if(strJson.Length > 0)
 {
//Success
 }

Output desired & also coming in SQL.

[{"Trucknumber":"HP09A3487","CompData":[{"TruckId":37886,"CompNo":1,"Capacity":6000},{"TruckId":37886,"CompNo":2,"Capacity":4000},{"TruckId":37886,"CompNo":3,"Capacity":3000},{"TruckId":37886,"CompNo":4,"Capacity":5000}]}]

Output coming in C# with extra '{', which is invalid for inserting into another Table.

{[{"Trucknumber":"HP09A3487","CompData":[{"TruckId":37886,"CompNo":1,"Capacity":6000},{"TruckId":37886,"CompNo":2,"Capacity":4000},{"TruckId":37886,"CompNo":3,"Capacity":3000},{"TruckId":37886,"CompNo":4,"Capacity":5000}]}]}

Any suggestion?

CodePudding user response：

I reproduced your setup but couldn't reproduce any problem. I suspect you're doing this to look at your data:

Visual Studio is adding the outermost { } purely for display purposes in the debugger. They aren't part of your data. Here my data definitely has no {} in:

It does this to every object you inspect:

A StringBuilder has an overriden ToString that shows the contents instead of the type name (the default behavior of object's ToString), so the contents of the SB appear in the debugger tooltip, inside of { }. Because { looks like JSON you're getting confused..

See the difference in the immediate window, in "object representation" versus "string representation"

Note, those \ before the " in the string rep aren't in your data either; they're added when visualizing strings to make it easier to paste a string into code as a literal

If you want to truly look at your data without these manipulations, put it into a string and then click the magifying glass in the tooltip of the StringBuilder (there are various options for visualizing it):

You see the string in a textbox, as you would if you wrote it to a file and opened it in Notepad

^{If this visualization confusion isn't the actual problem, post a screenshot of how youre determining your data has extra {} so we can see your thought process and work back from it}