Read and process JSON file with C#

Question

Dear Sir or Madam,

I have a JSON file available and would like to read and process this data using C# programming language. After reading the data from the JSON file, I would rewrite (change format) this data. You can think of it like a translator reading a JSON file and then translating it to another file.

An example would be:
I have a JSON file with different employee names (first name and last name), ID, location and etc. now the first thing I want to do is read the data using C# programming language. Then I want to rewrite the read data. For example in the JSON file residence is defined as City, but in the other file (like DB file) it is defined as CityOfResidence. Thus I must translate the indication from the JSON file. Finally after translation another file format will be generated, for example DB file.

Do any of you have a good suggestion on the most clever way to solve this problem?

With kind regards

Answer 1

.NET Core sample with StreamReader/StreamWriter.

    class Program  
    {  
        static void Main(string[] args)  
        {  
            List<Person> source = new List<Person>();  
  
            using (StreamReader r = new StreamReader("data.json"))  
            {  
                string json = r.ReadToEnd();  
                source = JsonSerializer.Deserialize<List<Person>>(json);  
            }  
  
            List<DataReadyPerson> destination = source.Select(d => new DataReadyPerson  
            {  
                CityOfResidence = d.City,  
                fname = d.Firstname,  
                lname = d.Lastname,  
                DataReadPersonId = d.Id  
            }).ToList();  
  
  
            string jsonString = JsonSerializer.Serialize(destination, new JsonSerializerOptions() { WriteIndented = true});  
            using (StreamWriter outputFile = new StreamWriter("dataReady.json"))  
            {  
                outputFile.WriteLine(jsonString);  
            }  
        }  
    }  
  
    public class Person  
    {  
        public int Id { get; set; }  
        public string Firstname { get; set; }  
        public string Lastname { get; set; }  
        public string City { get; set; }  
    }  
    public class DataReadyPerson  
    {  
        public int DataReadPersonId { get; set; }  
        public string fname { get; set; }  
        public string lname { get; set; }  
        public string CityOfResidence { get; set; }  
    }  

JSON input

[  
  {  
    "Id": 1,  
    "Firstname": "Hello",  
    "Lastname": "World",  
    "City": "New York"  
  },  
  {  
    "Id": 2,  
    "Firstname": "Foo",  
    "Lastname": "Bar",  
    "City": "Phoenix"  
  }  
]  

JSON Output

[  
  {  
    "DataReadPersonId": 1,  
    "fname": "Hello",  
    "lname": "World",  
    "CityOfResidence": "New York"  
  },  
  {  
    "DataReadPersonId": 2,  
    "fname": "Foo",  
    "lname": "Bar",  
    "CityOfResidence": "Phoenix"  
  }  
]  
  

Answer 2

SQL Server 2016 and above imports JSON documents directly into a table.

Import JSON documents into SQL Server

Once the data is imported, write a standard INSERT INTO whatever table you like using basic SQL.

INSERT INTO dbo.SomeTable (CityOfResidence)  
SELECT City FROM dbo.JsonImport  

If you must convert the JSON to a C# type first then create a C# type that matches the JSON. Visual Studio has a code generator for this purpose. First, copy the JSON. In Visual Studio select Edit -> Paste Special -> Paste JSON as Classes. From here you can use standard C# deserialization. Use NewtonSoft in .NET 4+ projects or the System.Text.Json namespace for .NET Core. Similar to the SQL example above table, once you have the deserialized data you can convert the C# type to whatever you like. How you go about this depends on how your project works. Do you use LINQ? A data access layer? ADO.NET? How do you currently work with a database?

There are other options like LINQ to JSON and JOSN file readers and writers. These depend on the type of project you are building.

Answer 3

So this I believe is what you are after in a mock-up

using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;

namespace CodeSampleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            // let's say this is not empty we got data from a source
            List<Person> people = new List<Person>();

            List<Student> students = people.Select(person => new Student()
            {
                Identifier = person.Id,
                First = person.FirstName,
                Last = person.LastName,
                CityLocation = person.City
            }).ToList();

            /*
             * Now either save to json or write to a database e.g.
             * using a managed data provider or via Entity Framework.
             */
            string json = JsonConvert.SerializeObject(students, Formatting.Indented);
        }
    }

    public class Person
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string City { get; set; }
    }
    public class Student
    {
        public int Identifier { get; set; }
        public string First { get; set; }
        public string Last { get; set; }
        public string CityLocation { get; set; }
    }
}