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#? or What is the simplest C# function to parse a JSON string into an object? or Can I LINQ a JSON?. — dbc
– dbc, Commented Dec 19, 2015 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
– André Alçada Padez, Commented Dec 19, 2015 at 18:26

Alberto Monteiro · Accepted Answer · 2015-12-19 19:19:46Z

1

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

edited Dec 19, 2015 at 19:19

answered Dec 19, 2015 at 18:46

Alberto Monteiro

6,2792 gold badges31 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Connor · Accepted Answer · 2015-12-20 03:05:16Z

1

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

edited Dec 20, 2015 at 3:05

answered Dec 19, 2015 at 19:03

Connor

8971 gold badge10 silver badges21 bronze badges

3 Comments

André Alçada Padez Over a year ago

Thank you, I will test it later with my real data

Sphinxxx Over a year ago

+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?

Connor Over a year ago

@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.

Collectives™ on Stack Overflow

parsing JSON data with LINQ

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related