How to covert JSON with nested array to CSV using ChoETL

Question

I have a JSON file with nested array:

{
  "Member": [
    {
      "ExtractDate": "2024-03-18T13:29:50Z",
      "Information": {
        "Surname": "Smith",
        "FirstName": "John",
        "DateOfBirth": "1960-03-12",
        "Email": "[email protected]",
        "Telephone": "01234-123456",
        "Address": {
          "Line1": "1 Road",
          "Line2": "District",
          "Zipcode": "001555"
        },
        "Employment": [
          {
            "EmployerName": "AAA Ltd",
            "StartDate": "1988-04-01",
            "EndDate": "1990-10-15"
          },
          {
            "EmployerName": "ABC Ltd",
            "StartDate": "1991-01-25",
            "EndDate": "1995-11-30"
          }
        ]
      },
      "AdditionalInfo": [
        {
          "Type": "A",
          "AlternateName": "Name",
          "AdditionalMessage": [
            "A",
            "B",
            "C"
          ]
        },
        {
          "Type": "A",
          "AlternateName": "Name",
          "AdditionalMessage": [
            "A",
            "B",
            "C"
          ]
        }
      ]
    },
    {
      "ExtractDate": "2024-03-18T13:29:50Z",
      "Information": {
        "Surname": "John",
        "FirstName": "Joe",
        "DateOfBirth": "1960-03-12",
        "Email": "[email protected]",
        "Telephone": "01234-123456",
        "Address": {
          "Line1": "1 Road",
          "Line2": "District",
          "Zipcode": "001555"
        },
        "Employment": [
          {
            "EmployerName": "AAA Ltd",
            "StartDate": "1988-04-01",
            "EndDate": "1990-10-15"
          },
          {
            "EmployerName": "ABC Ltd",
            "StartDate": "1991-01-25",
            "EndDate": "1995-11-30"
          }
        ]
      },
      "AdditionalInfo": [
        {
          "Type": "A",
          "AlternateName": "Name",
          "AdditionalMessage": [
            "A",
            "B",
            "C"
          ]
        },
        {
          "Type": "A",
          "AlternateName": "Name",
          "AdditionalMessage": [
            "A",
            "B",
            "C"
          ]
        }
      ]
    }
  ]
} 

I have mapped it to C# classes:

    public class AdditionalInfo
    {
        [JsonProperty("Type")]
        public string Type { get; set; }

        [JsonProperty("AlternateName")]
        public string AlternateName { get; set; }

        [JsonProperty("AdditionalMessage")]
        public List<string> AdditionalMessage { get; set; }
    }

    public class Address
    {
        [JsonProperty("Line1")]
        public string AddressLine1 { get; set; }

        [JsonProperty("Line2")]
        public string AddressLine2 { get; set; }

        [JsonProperty("Zipcode")]
        public string Zipcode { get; set; }
    }

    public class Employment
    {
        [JsonProperty("EmployerName")]
        public string EmployerName { get; set; }

        [JsonProperty("StartDate")]
        public string EmploymentStartDate { get; set; }

        [JsonProperty("EndDate")]
        public string EmploymentEndDate { get; set; }
    }

    public class Information
    {
        [JsonProperty("Surname")]
        public string Surname { get; set; }

        [JsonProperty("FirstName")]
        public string FirstName { get; set; }

        [JsonProperty("DateOfBirth")]
        public string DateOfBirth { get; set; }

        [JsonProperty("Email")]
        public string Email { get; set; }

        [JsonProperty("Telephone")]
        public string Telephone { get; set; }

        [JsonProperty("Address")]
        public Address Address { get; set; }

        [JsonProperty("Employment")]
        public List<Employment> Employment { get; set; }
    }

    public class Member
    {
        [JsonProperty("ExtractDate")]
        public DateTime ExtractDate { get; set; }

        [JsonProperty("Information")]
        public Information Information { get; set; }

        [JsonProperty("AdditionalInfo")]
        public List<AdditionalInfo> AdditionalInfo { get; set; }
    }

    public class Root
    {
        [JsonProperty("Member")]
        public List<Member> Member{ get; set; }
    }

I want to use ChoETL to convert it to a CSV file.

I am expecting following output:

ExtractDate,Surname,FirstName,DateOfBirth,Email,Telephone,AddressLine1,AddressLine2,Zipcode,EmployerName,EmplymentStartDate,EmplymentStartEndDate,EmployerName2,EmplymentStartDate2,EmplymentStartEndDate2,Type,AlternateName,AdditionalMessage
2024-03-18 13:29:50,Smith,John,1960-03-12,[email protected],01234-123456,1 Road,District,001555,AAA Ltd,1988-04-01,1990-10-15,ABC Ltd,1991-01-25,1995-11-30,A,Name,A+B+C
2024-03-18 13:29:50,John,Joe,1960-03-12,[email protected],01234-123456,1 Road,District,001555,AAA Ltd,1988-04-01,1990-10-15,ABC Ltd,1991-01-25,1995-11-30,A,Name,A+B+C

Some of the field names are required to change if multiple items e.g. Employment, Employment2, Employment3 and so on. Some value is string concatenation e.g. A+B+C. How to configure ChoETL to meet such requirements.

Answer 1

This is quite complex ask, to convert the given JSON to expected CSV file. Will require lot of code lift to accomblish it.

Step 1: Create converters for Employment and AdditionalInfo members to handle the expected CSV output from them. Here are

Employment Converter:

public class EmploymentConverter : IChoValueConverter, IChoHeaderConverter, IChoCollectionConverter
{
    public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
    {
        return value;
    }

    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
    {
        var list = (value as ICollection<Employment>).Take(2).ToList();
        if (list.Count() < 2)
        {
            list.Add(new Employment());
        }
        return list.Select(f => new object[] { f.EmployerName, f.EmploymentStartDate, f.EmploymentEndDate }).Unfold().ToArray();
    }

    public string GetHeader(string name, string fieldName, object parameter, CultureInfo culture)
    {
        return "EmployerName,EmplymentStartDate,EmplymentStartEndDate,EmployerName2,EmplymentStartDate2,EmplymentStartEndDate2";
    }

}

AdditionalInfo Converter:

public class AdditionalInfoConverter : IChoValueConverter, IChoHeaderConverter, IChoCollectionConverter
{
    public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
    {
        return value;
    }

    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
    {
        var item = (value as ICollection<AdditionalInfo>).FirstOrDefault();
        if (item == null)
            item = new AdditionalInfo();
        
        var list = new AdditionalInfo[] { item  };
        return list.Select(f => new object[] { f.Type, f.AlternateName, String.Join("+", f.AdditionalMessage) }).Unfold().ToArray();
    }

    public string GetHeader(string name, string fieldName, object parameter, CultureInfo culture)
    {
        return "Type,AlternateName,AdditionalMessage";
    }

}   

Step 2: Decorate them to corresponding properties in POCO class

public class AdditionalInfo
{
    [JsonProperty("Type")]
    public string Type { get; set; }

    [JsonProperty("AlternateName")]
    public string AlternateName { get; set; }

    [JsonProperty("AdditionalMessage")]
    public List<string> AdditionalMessage { get; set; }
}

public class Address
{
    [JsonProperty("Line1")]
    [ChoCSVRecordField]
    public string AddressLine1 { get; set; }

    [JsonProperty("Line2")]
    [ChoCSVRecordField]
    public string AddressLine2 { get; set; }

    [JsonProperty("Zipcode")]
    [ChoCSVRecordField]
    public string Zipcode { get; set; }
}

public class Employment
{
    [JsonProperty("EmployerName")]
    public string EmployerName { get; set; }

    [JsonProperty("StartDate")]
    public string EmploymentStartDate { get; set; }

    [JsonProperty("EndDate")]
    public string EmploymentEndDate { get; set; }
}

public class Information
{
    [JsonProperty("Surname")]
    [ChoCSVRecordField]
    public string Surname { get; set; }

    [JsonProperty("FirstName")]
    [ChoCSVRecordField]
    public string FirstName { get; set; }

    [JsonProperty("DateOfBirth")]
    [ChoCSVRecordField]
    public string DateOfBirth { get; set; }

    [JsonProperty("Email")]
    [ChoCSVRecordField]
    public string Email { get; set; }

    [JsonProperty("Telephone")]
    [ChoCSVRecordField]
    public string Telephone { get; set; }

    [JsonProperty("Address")]
    [ChoCSVRecordField]
    public Address Address { get; set; }

    [JsonProperty("Employment")]
    [ChoTypeConverter(typeof(EmploymentConverter))]
    [ChoCSVRecordField(QuoteField = false)]
    public List<Employment> Employment { get; set; }
}

public class Member
{
    [ChoCSVRecordField]
    [JsonProperty("ExtractDate")]
    public DateTime ExtractDate { get; set; }

    [ChoCSVRecordField]
    [JsonProperty("Information")]
    public Information Information { get; set; }

    [JsonProperty("AdditionalInfo")]
    [ChoTypeConverter(typeof(AdditionalInfoConverter))]
    [ChoCSVRecordField(QuoteField = false)]
    public List<AdditionalInfo> AdditionalInfo { get; set; }
}

Step 3: Finally using ChoETL, you can convert JSON to CSV as below

using (var r = ChoJSONReader<Member>.LoadText(json)
       .WithJSONPath("Member")
       .Configure(c => c.FlattenByNodeName = "Information")
        .UseJsonSerialization()
      )
{
    using (var w = new ChoCSVWriter<Member>(Console.Out)
           .WithFirstLineHeader()
           .Configure(c => c.TypeConverterFormatSpec.DateTimeFormat = "yyyy-MM-dd HH:mm:ss")
          )
    {
        w.Write(r);
    }

}

Sample fiddle: https://dotnetfiddle.net/G5pKSf