Reading JSON data in groups

Question

I have JSON data (households with their cars) which I am trying to read JSON data. A subset looks like this:

[
   {
      "car":[
         "Honda Civic",
         "Toyota Camry"
      ]
   },
   {
      "car":[
         "Honda Civic"
      ]
   },
   {
      "car":[
         "BMW 760",
         "Mercedes S",
         "Smart Car"
      ]
   },
   {
      "car":[
         "Honda Odyssey",
         "Tesla X"
      ]
   },
   {
      "car":[
         "BMW 760"
      ]
   },
   {
      "car":[
         "Honda Odyssey",
         "Tesla X"
      ]
   },
   {
      "car":[
         "BMW 760"
      ]
   },
   {
      "car":[
         "Toyota Camry",
         "Honda Civic"
      ]
   }
]

When I read the file using the following logic, it is read successfully. (I am using Newtonsoft.Json.)

string sJSON = File.ReadAllText(@"D:\MyFolder\cars.json");
List<Car> allCars = JsonConvert.DeserializeObject<List<Car>>(sJSON);

Cars class is this:

public class Car
{
    private ArrayList carNames = new ArrayList();

    public void AddCar(string carName)
    {
        carNames.Add(carName);
    }
}

There are two problems I am facing:

Though JSON is read successfully, and it recognizes car names, but is not adding them correctly to allCars.
How can I sum the number of cars? For example:
- households who have only BMW 760 are 3
- with Civic and Camry are 2
- with only Civic is 1, etc.

I tried to do what is mentioned on this question, but that did not work.

@KunalMukherjee Because I can build it dynamically, and thought it might help in adding the cars. — Farhan, May 24 '19 at 16:37
"households who have only BMW 760" is 2. 2 households have only 2, but 3 household have at least 1. — jwatts1980, May 24 '19 at 16:59

score 0 · Answer 1 · answered May 24 '19 at 16:45

You have to first flatten the nested list of car names and then group them to get your desired output.

class Program
{
    static void Main(string[] args)
    {
        string carsData = @"
                        [
               {
                  'car':[
                     'Honda Civic',
                     'Toyota Camry'
                  ]
               },
               {
                  'car':[
                     'Honda Civic'
                  ]
               },
               {
                  'car':[
                     'BMW 760',
                     'Mercedes S',
                     'Smart Car'
                  ]
               },
               {
                  'car':[
                     'Honda Odyssey',
                     'Tesla X'
                  ]
               },
               {
                  'car':[
                     'BMW 760'
                  ]
               },
               {
                  'car':[
                     'Honda Odyssey',
                     'Tesla X'
                  ]
               },
               {
                  'car':[
                     'BMW 760'
                  ]
               },
               {
                  'car':[
                     'Toyota Camry',
                     'Honda Civic'
                  ]
               }
            ]
        ";

        List<Car> allCars = JsonConvert.DeserializeObject<List<Car>>(carsData);

        // Flatten all the car names first then group them
        var carDistributions = allCars.SelectMany(x => x.CarNames)
               .GroupBy(x => x, x => x, (key, x) => new
               {
                   CarName = key,
                   Count = x.Count()
               })
               .ToList();

        foreach (var carDistribution in carDistributions)
        {
            Console.WriteLine(carDistribution.CarName + " " + carDistribution.Count);
        }


    }
}

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

Outputting:

Honda Civic 3
Toyota Camry 2
BMW 760 3
Mercedes S 1
Smart Car 1
Honda Odyssey 2
Tesla X 2

score 0 · Answer 2 · answered May 24 '19 at 16:54

First of all use json2csharp or PasteSpecial option to create model for json parsing.

PasteSpecial option you can find at Edit -> Paste Special -> Paste JSON As Classes

This will give you right model to parse your json string i.e.

public class Car
{
    public List<string> car { get; set; }
}

Now As you used code for Json deserialization, use same code.

 List<Car> allCars = JsonConvert.DeserializeObject<List<Car>>(sJSON);

Using Linq SelectMany() and where() you can get all record of cars based on its name, now use simple Count() and you will get count of each car from Json Array

 int count = allCars.SelectMany(x => x.car).Where(x => x == "Honda Civic").Count(); // It will return 3 as a result

jwatts1980 · Answer 3 · 2019-05-24T17:58:30.127

The native datatype for this JSON result is List<Dictionary<string, string[]>>. You can parse it directly with:

var cars = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string[]>>>(json);

And then you can create some functions to search on that data:

private int HouseholdWith(List<Dictionary<string, string[]>> cars, string car1)
{
    return cars.Count(household => household["car"].Any(c => c == car1));
}

private int HouseholdWith(List<Dictionary<string, string[]>> cars, string car1, string car2)
{
    return cars.Count(household => household["car"].Any(c => c == car1) && household["car"].Any(c => c == car2));
}

private int HouseholdWithOnly(List<Dictionary<string, string[]>> cars, string car)
{
    return cars.Count(household => household["car"].All(c => c == car));
}

If you want to reorganize the data from the JSON into Households, you can do something like this:

class Household
{
    public List<string> Cars { get; set; }
}

var cars = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string[]>>>(json);
List<Household> households = (
    from h in cars
    select new Household()
    {
        Cars = h["car"].ToList()
    }
).ToList();

With the modified search functions:

private int HouseholdsWith(List<Household> households, string car1)
{
    return households.Count(h => h.Cars.Any(c => c == car1));
}

private int HouseholdsWith(List<Household> households, string car1, string car2)
{
    return households.Count(h => h.Cars.Any(c => c == car1) && h.Cars.Any(c => c == car2));
}

private int HouseholdsWithOnly(List<Household> households, string car)
{
    return households.Count(h => h.Cars.All(c => c == car));
}

And to test:

Console.WriteLine("Households who have only BMW 760 are {0}", HouseholdsWithOnly(households, "BMW 760"));
//Households who have only BMW 760 are 2

Console.WriteLine("Households who have BMW 760 are {0}", HouseholdsWith(households, "BMW 760"));
//Households who have BMW 760 are 3

Console.WriteLine("Households with Civic and Camry are {0}", HouseholdsWith(households, "Honda Civic", "Toyota Camry"));
//Households with Civic and Camry are 2

Console.WriteLine("Households with only Civic is {0}", HouseholdsWithOnly(households, "Honda Civic"));
//Households with only Civic is 1

IMHO, your deserialization classes for Newtonsoft should be as simple as possible. Newtonsoft is powerful, and you can do a lot during the deserialization process, but the simpler it is, the easier it will be to make modifications if the structure of the data needs to change in the future. Your mapping function after deserialization is where you transform the data into something useful for your application. I think it's a good SoC principle.

Bonus Round

private void CreateReport(List<Household> households)
{
    //get all unique cars
    List<string> cars = households.SelectMany(h => h.Cars).Distinct().OrderBy(c => c).ToList();
    foreach(string c in cars)
    {
        Console.WriteLine("Households with {0}: {1}", c, HouseholdsWith(households, c));
        Console.WriteLine("Households with only {0}: {1}", c, HouseholdsWithOnly(households, c));
    }

    //Get each unique pair
    var pairs = households.Where(h => h.Cars.Count > 1).SelectMany(h =>
    {
        List<Tuple<string, string>> innerpairs = new List<Tuple<string, string>>();
        for (int i = 0; i < h.Cars.Count - 1; i++)
        {
            for (int j = i + 1; j < h.Cars.Count; j++)
            {
                if (string.Compare(h.Cars[i], h.Cars[j]) < 0)
                {
                    innerpairs.Add(new Tuple<string, string>(h.Cars[i], h.Cars[j]));
                }
                else
                {
                    innerpairs.Add(new Tuple<string, string>(h.Cars[j], h.Cars[i]));
                }
            }
        }
        return innerpairs;
    }).Distinct().ToList();

    foreach (var p in pairs)
    {
        Console.WriteLine("Households with {0} and {1}: {2}", p.Item1, p.Item2, HouseholdsWith(households, p.Item1, p.Item2));
    }
}

Produces an output like:

Households with BMW 760: 3  
Households with only BMW 760: 2

Households with Honda Civic: 3  
Households with only Honda Civic: 1

Households with Honda Odyssey: 2  
Households with only Honda Odyssey: 0

Households with Mercedes S: 1  
Households with only Mercedes S: 0

Households with Smart Car: 1  
Households with only Smart Car: 0

Households with Tesla X: 2  
Households with only Tesla X: 0

Households with Toyota Camry: 2  
Households with only Toyota Camry: 0

Households with Honda Civic and Toyota Camry: 2 
Households with BMW 760 and Mercedes S: 1 
Households with BMW 760 and Smart Car: 1 
Households with Mercedes S and Smart Car: 1 
Households with Honda Odyssey and Tesla X: 2

Reading JSON data in groups

3 Answers3