I am trying to use code from this c# answer to Convert nested JSON to CSV but my project is VB.NET.
I tried few online converters but without much success. C# version works great but the VB.NET versions throw an InvalidCastException
. What might be the problem ?
My JSON:
{
"Response": [
[
{
"id": 136662306,
"symbol": "aaa",
"status": "ACTIVE",
"base": "731.07686321",
"amount": "6.95345994",
"timestamp": "1524781083.0",
"swap": "0.0",
"pl": "5127.4352653395923394"
},
{
"id": 137733525,
"symbol": "bbb",
"status": "ACTIVE",
"base": "636.75093128",
"amount": "1.1",
"timestamp": "1531902193.0",
"swap": "0.0",
"pl": "687.800226608"
}
]
]
}
My C# Version that works
JObject obj = JObject.Parse(json);
// Collect column titles: all property names whose values are of type JValue, distinct, in order of encountering them.
var values = obj.DescendantsAndSelf()
.OfType<JProperty>()
.Where(p => p.Value is JValue)
.GroupBy(p => p.Name)
.ToList();
var columns = values.Select(g => g.Key).ToArray();
// Filter JObjects that have child objects that have values.
var parentsWithChildren = values.SelectMany(g => g).SelectMany(v => v.AncestorsAndSelf().OfType<JObject>().Skip(1)).ToHashSet();
// Collect all data rows: for every object, go through the column titles and get the value of that property in the closest ancestor or self that has a value of that name.
var rows = obj
.DescendantsAndSelf()
.OfType<JObject>()
.Where(o => o.PropertyValues().OfType<JValue>().Any())
.Where(o => o == obj || !parentsWithChildren.Contains(o)) // Show a row for the root object + objects that have no children.
.Select(o => columns.Select(c => o.AncestorsAndSelf()
.OfType<JObject>()
.Select(parent => parent[c])
.Where(v => v is JValue)
.Select(v => (string)v)
.FirstOrDefault())
.Reverse() // Trim trailing nulls
.SkipWhile(s => s == null)
.Reverse());
// Convert to CSV
var csvRows = new[] { columns }.Concat(rows).Select(r => string.Join(",", r));
var csv = string.Join("
", csvRows);
My VB.NET version gives the following exception:
System.InvalidCastException: 'Unable to cast object of type 'WhereSelectEnumerableIterator`2
Dim obj As JObject = Nothing
obj = JObject.Parse(json)
Dim values = obj.DescendantsAndSelf().
OfType(Of JProperty)().
Where(Function(p) TypeOf p.Value Is JValue).
GroupBy(Function(p) p.Name).ToList()
Dim columns = values.[Select](Function(g) g.Key).ToArray()
Dim parentsWithChildren = values.SelectMany(Function(g) g).
SelectMany(Function(v) v.AncestorsAndSelf().
OfType(Of JObject)().Skip(1)).ToHashSet()
Dim rows = obj.DescendantsAndSelf().
OfType(Of JObject)().
Where(Function(o) o.PropertyValues().
OfType(Of JValue)().Any()).
Where(Function(o) o = obj OrElse Not parentsWithChildren.Contains(o)).
[Select](Function(o) columns.[Select](Function(c) o.AncestorsAndSelf().
OfType(Of JObject)().
[Select](Function(parent) parent(c)).
Where(Function(v) TypeOf v Is JValue).
[Select](Function(v) CStr(v)).
FirstOrDefault()).
Reverse().
SkipWhile(Function(s) s Is Nothing).
Reverse())
Dim csvRows = {columns}.Concat(rows).[Select](Function(r) String.Join(",", r)) ' HERE IS WHERE THE EXCEPTION OCCURS
Dim csv = String.Join(vbLf, csvRows)
Exception
{"Unable to cast object of type 'WhereSelectEnumerableIterator`2[Newtonsoft.Json.Linq.JObject,System.Collections.Generic.IEnumerable`1[System.String]]' to type 'System.Collections.Generic.IEnumerable`1[System.String[]]'."}
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…