LINQ to JSON - list of settings from a dynamic nested array

Question

Here's the json string I have.

{ "?xml" : { "@version" : "1.0", "@encoding" : "UTF-8" }, "DataFeed" : { "@FeedName" : "issuerDetails", "SecurityDetails" : { "Security" : { "SecurityID" : { "@idValue" : "AAPL-NSDQ", "@fiscalYearEnd" : "2016-12-31T00:00:00.00" }, "FinancialModels" : { "FinancialModel" : [{ "@id" : "780", "@name" : "Estimates - Energy", "@clientCode" : "A", "Values" : [{ "@name" : "EBITDA", "@clientCode" : "EBITDA", "@currency" : "C$", "Value" : [{ "@year" : "2014", "#text" : "555.64" }, { "@year" : "2015", "#text" : "-538.986" }, { "@year" : "2016", "#text" : "554.447" }, { "@year" : "2017", "#text" : "551.091" }, { "@year" : "2018", "#text" : "0" } ] }, { "@name" : "EPS", "@clientCode" : "EPS", "@currency" : "C$", "Value" : [{ "@year" : "2014", "#text" : "0" }, { "@year" : "2015", "#text" : "-1.667" }, { "@year" : "2016", "#text" : "-1.212" }, { "@year" : "2017", "#text" : "0.202" }, { "@year" : "2018", "#text" : "0" } ] }, { "@name" : "CFPS", "@clientCode" : "CFPS", "@currency" : "C$", "Value" : [{ "@year" : "2014", "#text" : "3.196" }, { "@year" : "2015", "#text" : "-0.207" }, { "@year" : "2016", "#text" : "0.599" }, { "@year" : "2017", "#text" : "2.408" }, { "@year" : "2018", "#text" : "0" } ] } ] } ] } } } } } 

How can I select #text data for EPS for the years 2015, 2016, 2017? Here is the query that I still have:

 JObject jsonFeed = JObject.Parse(jsonText); var query = from security in jsonFeed.SelectTokens("DataFeed.SecurityDetails.Security") .SelectMany(i => i.ObjectsOrSelf()) let finModels = security.SelectTokens("FinancialModels.FinancialModel") .SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault() where finModels != null select new { FinModelClientCode = (string)finModels.SelectToken("Values[1] .@clientCode "), FinModelYear2015 = (string)finModels.SelectToken("Values[1].Value[1] .@year "), FinModelValue2015 = (string)finModels.SelectToken("Values[1].Value[1].#text"), FinModelYear2016 = (string)finModels.SelectToken("Values[1].Value[2] .@year "), FinModelValue2016 = (string)finModels.SelectToken("Values[1].Value[2].#text"), FinModelYear2017 = (string)finModels.SelectToken("Values[1].Value[3] .@year "), FinModelValue2017 = (string)finModels.SelectToken("Values[1].Value[3].#text"), }; 

Here are the jsonExtensions that I use:

 public static class JsonExtensions { public static IEnumerable<JToken> DescendantsAndSelf(this JToken node) { if (node == null) return Enumerable.Empty<JToken>(); var container = node as JContainer; if (container != null) return container.DescendantsAndSelf(); else return new[] { node }; } public static IEnumerable<JObject> ObjectsOrSelf(this JToken root) { if (root is JObject) yield return (JObject)root; else if (root is JContainer) foreach (var item in ((JContainer)root).Children()) foreach (var child in item.ObjectsOrSelf()) yield return child; else yield break; } public static IEnumerable<JToken> SingleOrMultiple(this JToken source) { IEnumerable<JToken> arr = source as JArray; return arr ?? new[] { source }; } } 

The problem is that EPS will not always be in one position for the next company? Therefore, I want the query to search for the EPS client code and return values ​​for the years mentioned above, hopefully using the DRY method. Could you help me finish my inquiry?

NOTE: I actually load the XML string by converting it to JSON and then parsing it.

 XmlDocument doc = new XmlDocument(); doc.LoadXml(xmlString); jsonText = Newtonsoft.Json.JsonConvert.SerializeXmlNode(doc); JObject jsonFeed = JObject.Parse(jsonText); 

Answer 1

I think the easiest way is to deserialize your json for a specific object, as shown below

 var root = JsonConvert.DeserializeObject<RootObject>(jsonstring); 

Your model will be

 public class SecurityID { [JsonProperty("@idValue")] public string IdValue { get; set; } [JsonProperty("@iscalYearEnd")] public string FiscalYearEnd { get; set; } } public class Time { [JsonProperty("@year")] public string Year { get; set; } [JsonProperty("#text")] public string Text { get; set; } } public class FinancialModelItem { [JsonProperty("@name")] public string Name { get; set; } [JsonProperty("@clientCode")] public string ClientCode { get; set; } [JsonProperty("@currency")] public string Currency { get; set; } public List<Time> Value { get; set; } } public class FinancialModel { [JsonProperty("@id")] public string Id { get; set; } [JsonProperty("@name")] public string Name { get; set; } [JsonProperty("@clientCode")] public string ClientCode { get; set; } public List<FinancialModelItem> Values { get; set; } } public class FinancialModels { public List<FinancialModel> FinancialModel { get; set; } } public class Security { public SecurityID SecurityID { get; set; } public FinancialModels FinancialModels { get; set; } } public class SecurityDetails { public Security Security { get; set; } } public class DataFeed { [JsonProperty("@FeedName")] public string FeedName { get; set; } public SecurityDetails SecurityDetails { get; set; } } public class Xml { [JsonProperty("@version")] public string Version { get; set; } [JsonProperty("@encoding")] public string Encoding { get; set; } } public class RootObject { [JsonProperty("?xml")] public Xml Xml { get; set; } public DataFeed DataFeed { get; set; } } 

And now your request will be

  var result = root.DataFeed.SecurityDetails.Security.FinancialModels.FinancialModel .FirstOrDefault()?.Values .FirstOrDefault(x => x.Name == "EPS") .Value .Where(x => new[] { "2015", "2016", "2017" }.Contains(x.Year)) .Select(x => x.Text) .ToList(); 

Answer 2

What about:

 var jsonFeed = JObject.Parse(jsonText); var epsToken = jsonFeed.SelectToken("$..Values[?(@ .@name =='EPS')]"); var year2014 = epsToken.SelectToken("Value[?(@ .@year =='2014')].#text").ToString(); var year2015 = epsToken.SelectToken("Value[?(@ .@year =='2015')].#text").ToString(); var year2016 = epsToken.SelectToken("Value[?(@ .@year =='2016')].#text").ToString(); var year2017 = epsToken.SelectToken("Value[?(@ .@year =='2017')].#text").ToString(); 

---

A more general approach that will choose all years and values:

 var jsonFeed = JObject.Parse(jsonText); var epsToken = jsonFeed.SelectToken("$..Values[?(@ .@name =='EPS')]"); var years = epsToken.SelectToken("Value") .Select(i => new { Year = i.Value<string>("@year"), Value = i.Value<decimal>("#text") }); 

---

$.. means that we will search from the beginning of the document, iterate through all nodes, and look for the Values token, which @name is equal to EPS . Basically, between ?( And ) you enter a condition in which markers should match. @ means the current node, so @ .@name translates to current node which has child node with name '@name' (which we compared with EPS in the example).

More information on JPath can be found here: http://goessner.net/articles/JsonPath .

---

Notice that you updated your answer that deals with XML, so the basics are the same:

 XmlDocument doc = new XmlDocument(); doc.LoadXml(xmlString); var epsNode = doc.SelectSingleNode("//Values[@name='EPS']"); var years = epsNode.SelectNodes("Value") .Cast<XmlNode>() .Select(i => new { Year = i.Attributes["year"].Value, Value = decimal.Parse(i.InnerText) }); 

Did not test it on your XML. Also, keep in mind that i.Attributes["year"] may be null , so also check this.

Answer 3

You started with XML data ... why don't you just treat it like XML data.

 var name = "EPS"; var years = new[] { "2015", "2016", "2017" }; var xpath = $"//Values[@name='{name}']/Value[{String.Join(" or ", years.Select(y => $"@year='{y}'"))}]"; var values = doc.XPathSelectElements(xpath).Select(e => (decimal)e); 

---

Otherwise, if you must insist on working with it as json, you can do this:

 var name = "EPS"; var years = new[] { "2015", "2016", "2017" }; var jpath = $"$..Values[?(@ .@name =='{name}')].Value[?({String.Join(" || ", years.Select(y => $"@ .@year =='{y}'"))})].#text"; var values = jsonFeed.SelectTokens(jpath).Select(v => (decimal)v); 

Answer 4

If you are sure that this will be the json format, you can create a class that represents the json object and let some library (like JconConvert) do the parsing. It should be easy from there.