Get a sum of comple Json structure with linq

Question

I have a complex json structure (at least for me ) that looks like

{ "Assets": [{ "Name": "asset1", "Code": "SS-15", "Items": [{ "Name": "Item1", "KGs": 255, "Cartons": 1222, "Containers": 3 }, { "Name": "Item2", "KGs": 150, "Cartons": 2322, "Containers": 5 } ] }, { "Name": "asset2", "Code": "SA-23", "Items": [{ "Name": "Item1", "KGs": 88, "Cartons": 40, "Containers": 1 }, { "Name": "Item2", "KGs": 960, "Cartons": 710, "Containers": 31 } ] } ]}

I need to summarize globally how many KGs, Cartons and Containers are for each type of item, something like this:

[{ "unit": "KGs", "Item1": 343, "Item2": 1110 }, { "unit": "Cartons", "Item1": 1262, "Item2": 3032 }, { "unit": "Containers", "Item1": 4, "Item2": 36 }]

I have been using LINQ and so far I have something like:

object.SelectMany(x => x.Items.GroupBy(k => k.Name, m => m.KGs)).GroupBy(g => g.Key);

It kind of looks what I am looking for but is not giving me the info I need.

Note: I am deserializing the json to a class in my project.

If it isn't the info you needed, what is it giving you? Also, I wouldn't GroupBy inside your SelectMany, since you GroupBy again afterwards. — user4189129
– user4189129, Commented Dec 1, 2016 at 12:54
@Yatiac Will you always have Item1 and Item2 or possibly others as well? — CodingYoshi
– CodingYoshi, Commented Dec 1, 2016 at 16:00

The One · Accepted Answer · 2016-12-01 16:41:40Z

I don't know if you're going to laugh at this or what because it's so far away from what you where expecting, but the main problem I see in this code is that you're trying to transpose items in a collection (Item1, Item2) to properties in an object ("Item1": 4, "Item2": 36), that forces me to think about dynamic creation of objects by using ExpandoObject class:

(You have to install and import MoreLinq in order to use DistinctBy)

var unitsNames = o["Assets"].SelectMany(a => a["Items"]).DistinctBy(i=>i["Name"]).Select(i=>i["Name"].ToString()); var allUnits=o["Assets"].SelectMany(a=>a["Items"]); var kgs=GetExpandoObject(unitsNames, allUnits,"KGs"); var cartons = GetExpandoObject(unitsNames, allUnits, "Cartons"); var containers = GetExpandoObject(unitsNames, allUnits, "Containers"); List<ExpandoObject> res = new List<ExpandoObject>() { kgs as ExpandoObject,cartons as ExpandoObject,containers as ExpandoObject }; string jsonString = JsonConvert.SerializeObject(res);

...

 private static IDictionary<string, object> GetExpandoObject(IEnumerable<string> unitsNames, IEnumerable<JToken> allUnits, string concept) { var eo = new ExpandoObject() as IDictionary<string, Object>; eo.Add("unit", concept); foreach (var u in unitsNames) { var sum = allUnits.Where(un => un["Name"].ToString() == u).Sum(_ => (int)_[concept]); eo.Add(u, sum); } return eo; }

This is the result:

[ {"unit":"KGs","Item1":343,"Item2":1110}, {"unit":"Cartons","Item1":1262,"Item2":3032}, {"unit":"Containers","Item1":4,"Item2":36} ]

I'm not saying this is not possible to do with a single Linq query, but I didn't see it that clear, perhaps someone smarter and/or with more available time might achieve it

Collectives™ on Stack Overflow

Get a sum of comple Json structure with linq

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related