parsing JSON data with LINQ

Question

So, I have some data, which comes like this:

[
  {
    "Name": "Jonh ",
    "Order": [
      {
        "Product": {
          "Id": 8
        },
        "Quantity": 1
      },
      {
        "Product": {
          "Id": 19
        },
        "Quantity": 8
      }
    ]
  },
  {
    "Name": "Jane Doe 1",
    "Order": [
      {
        "Product": {
          "Id": 26
        },
        "Quantity": 7
      },
      {
        "Product": {
          "Id": 44
        },
        "Quantity": 2
      },
      {
        "Product": {
          "Id": 21
        },
        "Quantity": 6
      },
      {
        "Product": {
          "Id": 48
        },
        "Quantity": 2
      },
      {
        "Product": {
          "Id": 35
        },
        "Quantity": 2
      },
      {
        "Product": {
          "Id": 43
        },
        "Quantity": 1
      }
    ]
  }
]

UPDATE: the JSON is already parsed with NewtonSoft.Json.JsonConvert
I am completely new to Linq, i was able to do this in JavaScript. I need a linq query that extracts the sold products ordered by the most sold;
so: it aggregates every product and sums the quantity sold, and orders by the sum of the quantities.

This is what i have for now:

var products = clientSales.SelectMany(m => m.Order).Select(f=>f.Product.Id).Distinct();

which gives me a list of distinct productIds...

What JSON parsing library are you using? See [How can I parse JSON with C#?](http://stackoverflow.com/questions/6620165/how-can-i-parse-json-with-c) or [What is the simplest C# function to parse a JSON string into an object?](http://stackoverflow.com/questions/2859753) or [Can I LINQ a JSON?](http://stackoverflow.com/questions/18758361/can-i-linq-a-json). — dbc, Dec 19 '15 at 18:10
they look exactly like the JSON, I have a ClientSales Class, an Order Class and a Product Class — André Alçada Padez, Dec 19 '15 at 18:26

Alberto Monteiro · Accepted Answer · 2015-12-19T19:19:46.387

You were almost right, first you should use SelectMany in Order, then OrderByDescending in Quantity and finally Select to get product id, like the code below:

var products = clientSales.SelectMany(m => m.Order)
                          .OrderByDescending(x => x.Quantity)
                          .Select(p => p.Product.Id)
                          .Distinct();

Output:

You can see it working here: https://dotnetfiddle.net/6sb3VY

Connor · Answer 2 · 2015-12-20T03:05:16.907

Assuming you had the following classes:

public class Customer
{
    public string Name { get; set; }
    public List<Item> Order { get; set; }
}
public class Item
{
    public Product Product { get; set; }
    public int Quantity { get; set; }
}
public class Product
{
    public int Id { get; set; }
}

You can produce a list of product IDs and the quantity sold ordered by the quantity descending with the following:

 string json = "[{\"Name\": \"Jonh \",\"Order\": [{\"Product\": {\"Id\": 8},\"Quantity\": 1},{\"Product\": {\"Id\": 19},\"Quantity\": 8}]},{\"Name\": \"Jane Doe 1\",\"Order\": [{\"Product\": {\"Id\": 26},\"Quantity\": 7},{\"Product\": {\"Id\": 44},\"Quantity\": 2},{\"Product\": {\"Id\": 21},\"Quantity\": 6},{\"Product\": {\"Id\": 48},\"Quantity\": 2},{\"Product\": {\"Id\": 35},\"Quantity\": 2},{\"Product\": {\"Id\": 43},\"Quantity\": 1}]}]";

 var deserializedObject = JsonConvert.DeserializeObject<List<Customer>>(json);

 var groupedProducts = from product in deserializedObject.SelectMany(c => c.Order)
                       group product by product.Product.Id into grpProduct
                       select new
                       {
                            ProductId = grpProduct.Key,
                            Quantity = grpProduct.ToList().Sum(p => p.Quantity)
                       };

// Produces the ordered list of product IDs and quantity sold sorted by quantity descending
var orderedProducts = groupedProducts.OrderByDescending(p => p.Quantity).ToList();

This would end up producing the following list of anonymous objects based on your input:

Product ID      Quantity
19              8
26              7
21              6
44              2
48              2
35              2
8               1
43              1

+1 This will give the correct result when multiple orders have the same product. But I don't see why you need `...orderby grpProduct.Count() descending...` in `groupedProducts`? — Sphinxxx, Dec 19 '15 at 23:34
@Sphinxxx You're right. You don't need the orderby clause there since the ordering is really done in `orderedProducts`. I edited my answer to remove it. — Connor, Dec 20 '15 at 03:06

parsing JSON data with LINQ

2 Answers2