c#linqc#-4.0json.netlinq-to-json
JSON.Net - Another data retrieval from a polymorphic json string
Here's what my JSON string looks like:
{
"?xml" : {
"@version" : "1.0",
"@encoding" : "UTF-8"
},
"DataFeed" : {
"@FeedName" : "content",
"now" : "2016-10-17T14:11:38.00",
"Content" : [{
"ContentData" : {
"@statusType" : "Published",
"@isReleased" : "Yes",
"@docId" : "21756",
"ProductStatus" : {
"@productCategory" : "Issuer",
"@focus" : "Issuer",
"Symbol" : {
"@PrimarySymbol" : "GOOGL",
"@SecondarySymbol" : "Google",
"@BmId" : "1790"
}
},
"ContentVersion" : {
"@versionId" : "1",
"@lastVersion" : "true",
"@lastPublished" : "true",
"Url" : [{
"@type" : "XML",
"@size" : "146410",
"#cdata-section" : "https://aaa.yyy.com/docs/xml/708893eb-1fd9-4278-8b0e-e7e0738d8105.xml"
}, {
"@type" : "HTML",
"@size" : "46105",
"#cdata-section" : "https://aaa.yyy.com/docs/html/708893eb-1fd9-4278-8b0e-e7e0738d8105.html"
}, {
"@type" : "PDF",
"@size" : "1274885",
"#cdata-section" : "https://aaa.yyy.com/docs/pdf/708893eb-1fd9-4278-8b0e-e7e0738d8105.pdf"
}, {
"@type" : "ALTERNATE PDF",
"@size" : "60960",
"#cdata-section" : "https://aaa.yyy.com/docs/shortPdf/708893eb-1fd9-4278-8b0e-e7e0738d8105.pdf"
}
]
}
}
}, {
"ContentData" : {
"@statusType" : "Published",
"@isReleased" : "Yes",
"@docId" : "22457",
"ProductStatus" : {
"@productCategory" : "Issuer",
"@focus" : "Issuer",
"Symbol" : {
"@PrimarySymbol" : "AAPL",
"@SecondarySymbol" : "Apple",
"@BmId" : "1121"
}
},
"ContentVersion" : [{
"@versionId" : "1",
"@lastVersion" : "false",
"@lastPublished" : "false",
"Url" : [{
"@type" : "XML",
"#cdata-section" : "https://aaa.yyy.com/docs/version/708893eb-1fd9-4278-8b0e-e7e0738d8105.1476742297.xml"
}, {
"@type" : "HTML",
"#cdata-section" : "https://aaa.yyy.com/docs/version/708893eb-1fd9-4278-8b0e-e7e0738d8105.1476742297.html"
}, {
"@type" : "PDF",
"#cdata-section" : "https://aaa.yyy.com/docs/version/708893eb-1fd9-4278-8b0e-e7e0738d8105.1476742297.pdf"
}, {
"@type" : "ALTERNATE PDF",
"#cdata-section" : "https://aaa.yyy.com/docs/version/708893eb-1fd9-4278-8b0e-e7e0738d8105.1476742297_short.pdf"
}
]
}, {
"@versionId" : "2",
"@lastVersion" : "true",
"@lastPublished" : "true",
"Url" : [{
"@type" : "XML",
"@size" : "174520",
"#cdata-section" : "https://aaa.yyy.com/docs/xml/708893eb-1fd9-4278-8b0e-e7e0738d8105.xml"
}, {
"@type" : "HTML",
"@size" : "46105",
"#cdata-section" : "https://aaa.yyy.com/docs/html/708893eb-1fd9-4278-8b0e-e7e0738d8105.html"
}, {
"@type" : "PDF",
"@size" : "1270150",
"#cdata-section" : "https://aaa.yyy.com/docs/pdf/708893eb-1fd9-4278-8b0e-e7e0738d8105.pdf"
}, {
"@type" : "ALTERNATE PDF",
"@size" : "60960",
"#cdata-section" : "https://aaa.yyy.com/docs/version/708893eb-1fd9-4278-8b0e-e7e0738d8105.1577742297_short.pdf"
}
]
}
]
}
}, {
"ContentData" : {
"@statusType" : "Published",
"@isReleased" : "Yes",
"@docId" : "22501",
"ProductStatus" : {
"@productCategory" : "Issuer",
"@focus" : "Issuer",
"Symbol" : [{
"@PrimarySymbol" : "AAA",
"@SecondarySymbol" : "Some name 1",
"@BmId" : "561"
}, {
"@PrimarySymbol" : "ABC",
"@SecondarySymbol" : "Some Name 2",
"@BmId" : "29"
}, {
"@PrimarySymbol" : "BBB",
"@SecondarySymbol" : "Some Name 3",
"@BmId" : "36"
}, {
"@PrimarySymbol" : "CCC",
"@SecondarySymbol" : "Some Name 4",
"@BmId" : "624"
}
]
},
"ContentVersion" : {
"@versionId" : "1",
"@lastVersion" : "true",
"@lastPublished" : "true",
"Url" : [{
"@type" : "XML",
"@size" : "2706269",
"#cdata-section" : "https://aaa.yyy.com/docs/xml/1c420081-0ce5-4959-91d3-848bbbccd3e3.xml"
}, {
"@type" : "HTML",
"@size" : "32445",
"#cdata-section" : "https://aaa.yyy.com/docs/html/1c420081-0ce5-4959-91d3-848bbbccd3e3.html"
}, {
"@type" : "PDF",
"@size" : "887608",
"#cdata-section" : "https://aaa.yyy.com/docs/pdf/1c420081-0ce5-4959-91d3-848bbbccd3e3.pdf"
}, {
"@type" : "ALTERNATE PDF",
"@size" : "34366",
"#cdata-section" : "https://aaa.yyy.com/docs/shortPdf/1c420081-0ce5-4959-91d3-848bbbccd3e3.pdf"
}
]
}
}
}
]
}
}
I'm following the code example from LINQ to Json data retrieval from polymorphic json. I want to select the following but only if there is a single issuer in Symbol section and the PDF url for latest version:
@docId@PrimarySymbol@BmId#cdata-section(but only for PDFs for the latest version)
Here's what my query looks like:
from content in jsonFeed.SelectTokens("DataFeed.Content").SelectMany(i => i.ObjectsOrSelf())
let issuer = content
.SelectToken("ContentData.ProductStatus.@productCategory")
.SingleOrMultiple()
.Select(t => (string)t)
.ToArray()
where issuer.FirstOrDefault().Equals("Issuer")
select new
{
DocID = (string)content.SelectToken("ContentData.@docId"),
Symbol = (string)content.SelectToken("ContentData.ProductStatus.Symbol.@PrimarySymbol"),
BMId = (int)content.SelectToken("ContentData.ProductStatus.Symbol.@BmId"),
PDFUrl = (string)content.SelectToken("ContentData.ContentVersion.Url[3].#cdata-section")
}).ToList();
Right now, I can successfully get all fields for Google, but I can't get PDFUrl for Apple (because I don't know how to get to the latest version). Also, how can I complete my Where clause so that it will only select documents with 1 symbol? So, in the end, my list will be the image below:
Solution
You can combine let and where to calculate and filter on intermediate results.
The following query uses this technique, and also queries for the URL with @type equal to "PDF" rather than hardcoding it to be at index 3 (it's actually at index 2, by the way):
string pdfUrlType = "PDF";
var query =
(from content in jsonFeed.SelectTokens("DataFeed.Content").SelectMany(i => i.ObjectsOrSelf())
let issuer = content
.SelectToken("ContentData.ProductStatus.@productCategory")
.SingleOrMultiple()
.Select(t => (string)t)
.ToArray()
where issuer.FirstOrDefault().Equals("Issuer")
// Get the @PrimarySymbol where there is exactly one, or null otherwise.
let symbol = (string)content.SelectToken("ContentData.ProductStatus.Symbol.@PrimarySymbol")
// Skip content without a single primary symbol
where symbol != null
// Get the last ContentVersion record in the JSON document.
let lastContentVersion = content
.SelectToken("ContentData.ContentVersion")
.SingleOrMultiple()
.Where(v => (bool?)v.SelectToken("@lastVersion") == true)
.LastOrDefault()
// Skip content with no ContentVersion
where lastContentVersion != null
// Get the PDF Url record
let pdfUrl = lastContentVersion.SelectToken("Url")
.SingleOrMultiple()
.Where(u => (string)u.SelectToken("@type") == pdfUrlType)
.FirstOrDefault()
// Skip content with no PDF Url record
where pdfUrl != null
select new
{
DocID = (string)content.SelectToken("ContentData.@docId"),
Symbol = symbol,
BMId = (int?)content.SelectToken("ContentData.ProductStatus.Symbol.@BmId"),
PDFUrl = (string)pdfUrl.SelectToken("#cdata-section")
}).ToList();
If you actually want the alternate PDF url instead, set
string pdfUrlType = "ALTERNATE PDF";
Sample fiddle.
If you are sure that the the latest version of ContentVersion in each "Content" record (the one for which "@lastVersion" equals "true") is guaranteed to be the last one by document order, you can simplify the query as follows:
let lastContentVersion = content
.SelectToken("ContentData.ContentVersion")
.SingleOrMultiple()
.LastOrDefault()