How to get JSON data directly from the database table?-CodePudding

A column on my database table contains below JSON serialized string.

Table: school_details

school_id     class_json

25            {
              "class_id": "1377",
              "class_name": "XXX",
               "No.of students": "100"
              }

25                {
             "class_id": "1378",
             "class_name": "YYX",
             "No.of students": "80"
                }

Now I want to read this data and send it as a JSON array as shown in the 'Expected output' below. But I got a different output when doing like below. How should I change the code to get the desired output? Is this a good practice to store JSON directly as above in the database table? or else need to convert it to the string before storing it in the database?

   string school_id="25";
   string status="success";
   sql = "SELECT class_json FROM school_details WHERE school_id @school_id";

            cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@school_id", school_id);
            dr = cmd.ExecuteReader();
            var dt = new DataTable();
            if (dr.HasRows)
            {
               dt.Load(dr);
                status = "success";
            }
           var getData = new { status, school_id,Class_Deatils=dt };
        return getData;

Output:

{
  "status": "success",
  "school_id": "25",
  "Class_Deatils": [
 {
  "class_json ": "{\n  \"class_id\": \"1377\",\n  \"class_name\": \"XXX\",\n  \"No.of students\": \"100\"\n  }"
        },
 {
  "class_json ": "{\n  \"class_id\": \"1378\",\n  \"class_name\": \"YYX\",\n  \"No.of students\": \"80\"\n  }"
        }
  ]
  }

Expected Output:

{
  "status": "success",
  "school_id": "25",
  "Class_Deatils": [{
  "class_id": "1377",
  "class_name": "XXX",
  "No.of students": "100"
  },
  {
  "class_id": "1378",
  "class_name": "YYX",
  "No.of students": "80"
  }
  ]
  }

CodePudding user response：

I propose to you use serialization and deserialization for saving and retrieving JSON data from your database, it's can help you for preventing problems and you have good control over your JSON data. you can use the newtonsoft library for serialization and deserialization very easily.

you see the examples visit https://www.newtonsoft.com/json

CodePudding user response：

The result that you get in class_json is actually an escaped string, not a pure json.
If you want to use it as a json, you can use the JObject.Parse() method.

Here's how to do that:

using System;
using Newtonsoft.Json.Linq;
                    
public class Program
{
    public static void Main()
    {
        string class_json_str = "{\n  \"class_id\": \"1377\",\n  \"class_name\": \"XXX\",\n  \"No.of students\": \"100\"\n  }";
        JObject o = JObject.Parse(class_json_str );
        
        // Now you have a JSON objects which contains the data.
        // You can access it's properties like this 
        var classId = o["class_id"];
        Console.WriteLine(classId);

        // In case you want to make a string again, use the following
        var jsonString = o.ToString();
        Console.WriteLine(jsonString);
    }
}

P.S. The best practice would be to properly deserialize it as a class instead of playing with it as a json. And then serialize back when sending to the front-end. For more info see:
https://docs.microsoft.com/en-us/dotnet/standard/serialization/system-text-json-how-to