deserialize mongodb datetime field "$time" of JSON document field in C#/SSIS Script component

Question

I am trying to deserialize a mongodb's datetime "$time" field from a JSON file in Script Component SSIS/C# WITHOUT using newtonsoft. This is how far I have reached. Thanks for any help.

Example of my JSON file:

[ { "studentId":A2336, "LastUpdatedDateTime":{ "$date":"2022-08-12T20:11:30.324Z"}} , { "studentId":B1470, "LastUpdatedDateTime":{ "$date":"2021-03-02T21:22:44.310Z"} } ]

//current code
Public overridevoidCreateNewOutputRows(){
      String jsonFileContent = File.ReadAllText(@"C:\desktop\sample.json");
      javaScriptSerializer js = newjavaScriptSerializer();
      List<Student> allStudents = js.Deserialize<List<Student>>(jsonFileContent);
foreach(var student inallStudents)
          {
               Output0Buffer.AddRow();
               System.Windows.Forms.MessageBox.Show("Student Id is: "+ student.studentId.ToString());
               Output0Buffer.StudentId = student.studentId;
        
               //how to print/convert the LastUpdatedDateTime to the datetime Output0Buffer field?
              //this gives and error that says "Object reference not set to an instance of an object.
               System.Windows.Forms.MessageBox.Show("Student Id is: "+ student.LastUpdatedDateTime.date.ToString());
//this yields in "Student Id is: "
               System.Windows.Forms.MessageBox.Show("Student Id is: "+ student.LastUpdatedDateTime.date);

             //here I am trying to assign it to an ouput0buffer field
                  Output0Buffer.LastUpdatedDateTime = student.LastUpdatedDateTime.date;

    }
}


Class Student
{
    Public String studentId { get; set;}
    Public Jsondatetime LastUpdatedDateTime  {get; set;}
}

Class Jsondatetime
{
    Public String date { get; set;}
}

I have tried creating a different class for the "$date" like shown in the code above. It yeilds to and error when trying to print as a string and yeilds as a blank when trying to print as is. I expected the exact value of the key "$date".

Answer 1

Your json is not valid, you have to fix it by adding " to studentId. Since you are using an ancient deserializer, try this code

var jsonFileContent = "[ { \"studentId\":\"A2336\", \"LastUpdatedDateTime\":{ \"$date\":\"2022-08-12T20:11:30.324Z\"}} , { \"studentId\":\"B1470\", \"LastUpdatedDateTime\":{ \"$date\":\"2021-03-02T21:22:44.310Z\"} } ]";

jsonFileContent = jsonFileContent.Replace("\"$date\"", "\"date\"");

 List<Student> allStudents = js.Deserialize<List<Student>>(jsonFileContent);

DateTime date = allStudents[0].LastUpdatedDateTime.date;


public class Student
{
    public string studentId { get; set; }
    public LastUpdatedDateTime LastUpdatedDateTime { get; set; }
}
public class LastUpdatedDateTime
{
    public DateTime date { get; set; }
}