I have a polymorphic json string. Here's what it looks like:
{
"?xml":{
"@version":"1.0",
"@encoding":"UTF-8"
},
"DataFeed":{
"@FeedName":"AdminData",
"Issuer":[
{
"name":"Apple",
"symbol":"AAPL-O",
"active":"1",
"securities":{
"Security":{
"sedol":"B0XXF09",
"coverage":{
"Coverage":{
"analyst":{
"@firstName":"Steve",
"@lastName":"Jobs",
"@rank":"1"
}
}
},
"symbolMappingList":{
"SecuritySymbol":{
"symbolset":{
"id":"11",
"symbol":"ISIN",
"name":"ISIN",
"rixmlName":"ISIN",
"researchDirect":"S&P"
},
"symbol":"US44919P5XXX"
}
},
"symbolMapping":{
"entry":{
"int":"11",
"SecuritySymbol":{
"@reference":"../../../symbolMappingList/SecuritySymbol"
}
}
},
"customFields":{
"customField":[
{
"@name":"ADP",
"@type":"Textbox",
"values":{
"value":"H0192XX"
}
},
{
"@name":"Top 15",
"@type":"Dropdown, multiple choice",
"values":null
}
]
}
}
}
},
{
"name":"Microsoft",
"symbol":"MSFT-OTC",
"active":"1",
"securities":{
"Security":{
"sedol":"B8FW54",
"coverage":{
"Coverage":{
"analyst":{
"@firstName":"Bill",
"@lastName":"Gates",
"@rank":"1"
}
}
},
"symbolMappingList":{
"SecuritySymbol":[
{
"symbolset":{
"id":"3",
"symbol":"CUSIP",
"name":"CUSIP",
"rixmlName":"CUSIP",
"researchDirect":"S&P"
},
"symbol":"04316A1XX"
},
{
"symbolset":{
"id":"11",
"symbol":"ISIN",
"name":"ISIN",
"rixmlName":"ISIN",
"researchDirect":"S&P"
},
"symbol":"US04316A10XX"
}
]
},
"symbolMapping":{
"entry":[
{
"int":"3",
"SecuritySymbol":{
"@reference":"../../../symbolMappingList/SecuritySymbol"
}
},
{
"int":"11",
"SecuritySymbol":{
"@reference":"../../../symbolMappingList/SecuritySymbol[2]"
}
}
]
},
"customFields":{
"customField":[
{
"@name":"ADP Security Code",
"@type":"Textbox",
"values":null
},
{
"@name":"Top 15",
"@type":"Dropdown, multiple choice",
"values":null
}
]
}
}
}
}
]
}
}
Someone once helped me with an extension class so that I can retrieve the ADP codes. Here's the extension class:
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;
}
}
Based on that, here's my query:
var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
where (string)issuer["active"] == "1"
let security = issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
let securitySymbol = issuer.SelectTokens("securities.Security.symbolMappingList")
.SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
where security != null
select new
{
CompName = (string)issuer["name"],
SEDOL = ((string)security["sedol"]).StartsWith("0") ?
String.Format("'{0}", (string)security["sedol"]) : (string)security["sedol"],
ADP = security["customFields"]
.DescendantsAndSelf()
.OfType<JObject>()
.Where(o => (string)o["@name"] == "ADP Security Code")
.Select(o => (string)o.SelectToken("values.value"))
.FirstOrDefault(),
ISIN = security["symbolMappingList"]
.DescendantsAndSelf()
.OfType<JObject>()
.Where(o => (string)o["SecuritySymbol.symbolset.name"] == "ISIN")
.Select(o => (string)o.SelectToken("SecuritySymbol.symbol"))
.FirstOrDefault()
};
I'm able to get the ADP codes. But how can I get the ISIN codes? I think I'm pretty close, but I get all nulls. What do I need to change to make this work?
It should be:
ISIN = security["symbolMappingList"]
.DescendantsAndSelf()
.OfType<JObject>()
.Where(o => (string)o.SelectToken("symbolset.name") == "ISIN")
.Select(o => (string)o.SelectToken("symbol"))
.FirstOrDefault()
Some notes:
You cannot use indexers to select deeply nested tokens, as you try to do with the expression o["SecuritySymbol.symbolset.name"]. Indexers will only return immediate children. You need to use SelectToken() to select grandchildren.
The value of the "SecuritySymbol" property is sometimes an object:
"SecuritySymbol":{
"symbolset":{
And sometimes an array:
"SecuritySymbol":[
{
"symbolset":{
Depending upon the number of items therein. Because of this polymorphism, you cannot just do SelectToken("SecuritySymbol.symbolset.name"). Instead use DescendantsAndSelf() to recursively search the value of SecuritySymbol and its descendants (whether embedded in an array or not) for an object with the appropriate "symbolset.name" child token.
Here is an updated query that has been slightly optimized to eliminate duplicate calls to SelectToken and also to filter on the "sedol" name:
var filterString = "B0XXF09"; // Null if filtering is not desired
var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
where (string)issuer["active"] == "1"
let security = issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
where security != null
let sedol = (string)security["sedol"]
where (sedol != null && filterString == null || sedol.Contains(filterString))
select new
{
CompName = (string)issuer["name"],
SEDOL = sedol.StartsWith("0") ? String.Format("'{0}", sedol) : sedol,
ADP = security["customFields"]
.DescendantsAndSelf()
.OfType<JObject>()
.Where(o => (string)o["@name"] == "ADP Security Code")
.Select(o => (string)o.SelectToken("values.value"))
.FirstOrDefault(),
ISIN = security["symbolMappingList"]
.DescendantsAndSelf()
.OfType<JObject>()
.Where(o => (string)o.SelectToken("symbolset.name") == "ISIN")
.Select(o => (string)o.SelectToken("symbol"))
.FirstOrDefault()
};