Here's the json string that 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 the #text data for EPS for years 2015, 2016, 2017? Here's the query that I have so far:
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's the jsonExtensions I'm using:
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 the same position for the next company? So, I want the query to search for EPS clientcode & return the values for the years mentioned above, hopefully using the DRY method. Would you be so kind as to help me finish up my query?
NOTE: I'm actually downloading a XML string, 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);
I think the easiest way would be deserializing your json to a concrete object like below
var root = JsonConvert.DeserializeObject<RootObject>(jsonstring);
Your model would 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 your query would now 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();