将已解析的 JSON jobject 传递给静态扩展,它将返回展平后的 jobjects 数组,然后转换为 CSV。代码部分取自其他 Stack Overflow 和其他资源,我没有参考资料。
public static IEnumerable<string> JsonToCsvRowsWithHierarchyHeaders(string jsonData)
{
if (jsonData.Trim().StartsWith("[") && jsonData.Trim().EndsWith("]"))
{
var startString = "{\"appendRoot\":";
var endString = "}";
jsonData = $"{startString}{jsonData}{endString}";
}
var jObject = JsonConvert.DeserializeObject<JObject>(jsonData);
var flattenJObjects = JsonParserExtensions.FlattenJsonGetJObjects(jObject).ToList();
var csvRows = new List<string>();
if (flattenJObjects.Any())
{
var firstRow = flattenJObjects.First().Children<JProperty>().Select(x => x.Name).ToList();
var header = string.Join(delimeter, firstRow).Replace("appendRoot_", "").ToLower();
csvRows.Add(header);
foreach (var flattenJObject in flattenJObjects)
{
var tokens = flattenJObject.Children<JProperty>();
if (firstRow.Count() != tokens.Count())
{
var missMatchPropertyValues = firstRow.Select(cell => tokens.FirstOrDefault(x => x.Name == cell))
.Select(value => value == null
? string.Empty
: CheckAndUpdateRowCellValueTextQualifier(JsonConvert.DeserializeObject<string>(value.Value.ToString(Newtonsoft.Json.Formatting.None))))
.ToList();
var rowString = string.Join(delimeter, missMatchPropertyValues);
csvRows.Add(rowString);
}
else
{
var rowValue = tokens.Select(token =>
CheckAndUpdateRowCellValueTextQualifier(
JsonConvert.DeserializeObject<string>(
token.Value.ToString(Newtonsoft.Json.Formatting.None))));
var rowString = string.Join(delimeter, rowValue);
csvRows.Add(rowString);
}
}
}
return csvRows;
}
private static string CheckAndUpdateRowCellValueTextQualifier(string value)
{
const string q = @"""";
if (!string.IsNullOrEmpty(value) && value.Contains('\"'))
{
return value;
}
if (!string.IsNullOrEmpty(value) && (value.Contains(',') ||
value.Contains('"') ||
value.Contains('\n') || value.Contains('\r')))
{
return $"{q}{value}{q}";
}
return value;
}
}
public static class JsonParserExtensions
{
public static IEnumerable<JObject> FlattenJsonGetJObjects(JObject jObject, string parentName = null)
{
if (!(parentName is null))
jObject = RenamePropertiesByHierarchyName(jObject, parentName);
var fields = jObject.Properties().Where(p => p.Value.GetType().Name == "JValue").ToList();
var objects = jObject.Properties().Where(p => p.Value.GetType().Name == "JObject").ToList();
var arrays = jObject.Properties().Where(p => p.Value.GetType().Name == "JArray").ToList();
var objectsArray = arrays.Where(array => array.Value.All(elements => elements.GetType().Name == "JObject")).ToList();
var valuesArray = arrays.Where(array => array.Value.All(elements => elements.GetType().Name == "JValue")).ToList();
var nestedObjects = ProcessNestedObjects(objects);
var joinedInnerObjects = nestedObjects.Any()
? nestedObjects.Select(innerObject => JoinJObject(new JObject(fields), innerObject))
: new List<JObject> { new JObject(fields) };
var arraysObjectList = GetJObjectsFromArrayOfJProperties(objectsArray);
var arraysValueList = GetJObjectsFromArrayOfValues(valuesArray);
var joinedAll = joinedInnerObjects.SelectMany(inner => JoinMultipleJObjects(arraysObjectList, arraysValueList, inner));
return joinedAll;
}
public static List<JObject> ProcessNestedObjects(List<JProperty> jObjects)
{
var processNestedObjects = new List<JObject>();
var renamedJObjects = jObjects?.Select(obj => RenamePropertiesByHierarchyName(obj.Value.ToObject<JObject>(), obj.Name)).ToList();
if (!(renamedJObjects?.Count > 0)) return processNestedObjects;
var renamed = renamedJObjects.Aggregate((acc, next) => JoinJObject(acc, next));
var nestedObjects = renamed.Properties().Where(p => p.Value.GetType().Name == "JObject").ToList();
var nestedArrays = renamed.Properties().Where(p => p.Value.GetType().Name == "JArray").ToList();
var nestedObjectsArray = nestedArrays.Where(array => array.Value.All(elements => elements.GetType().Name == "JObject")).ToList();
var nestedValuesArray = nestedArrays.Where(array => array.Value.All(elements => elements.GetType().Name == "JValue")).ToList();
nestedArrays.ForEach(p => renamed.Remove(p.Name));
nestedObjects.ForEach(p => renamed.Remove(p.Name));
var nestedObjectList = new List<JObject>();
var nestedMultipleObjectList = new List<JObject>();
foreach (var listJObjects in nestedObjects.Select(innerObject => FlattenJsonGetJObjects(innerObject.Value.ToObject<JObject>(), innerObject.Name)).ToList())
{
if (listJObjects.Count() > 1)
nestedMultipleObjectList.AddRange(listJObjects);
else
nestedObjectList.Add(listJObjects.First());
}
var jObjectsFromArrayOfJProperties = GetJObjectsFromArrayOfJProperties(nestedObjectsArray);
var jObjectsFromArrayOfValues = GetJObjectsFromArrayOfValues(nestedValuesArray);
var aggregate = nestedObjectList.Aggregate(renamed, (acc, next) => JoinJObject(acc, next));
var groupedNestedObjects = (nestedMultipleObjectList.Any()) ? nestedMultipleObjectList.Select(nested => JoinJObject(aggregate, nested))
: new List<JObject> { aggregate };
var groupedNestedObjectsList = groupedNestedObjects.Select(groupedNested => JoinMultipleJObjects(jObjectsFromArrayOfJProperties, jObjectsFromArrayOfValues, groupedNested));
processNestedObjects.AddRange(groupedNestedObjectsList.SelectMany(e => e));
return processNestedObjects;
}
public static List<JObject> JoinMultipleJObjects(List<JObject> nestedArraysObjectList, List<JObject> nestedArraysValueList, JObject groupedNestedObjects)
{
var result = new List<JObject>();
var joined = new List<JObject>();
if (!nestedArraysObjectList.Any())
joined.Add(groupedNestedObjects);
else
nestedArraysObjectList.ForEach(e => joined.Add(JoinJObject(groupedNestedObjects, e)));
result.AddRange(nestedArraysValueList.Any()
? nestedArraysValueList
.SelectMany(value => joined, (value, joinedItem) => JoinJObject(joinedItem, value)).ToList()
: joined);
return result;
}
public static List<JObject> GetJObjectsFromArrayOfJProperties(List<JProperty> nestedJProperties)
{
var fromArrayOfJProperties = new List<JObject>();
foreach (var jProperty in nestedJProperties)
{
var nestedArraysObjectList = new List<JObject>();
var name = jProperty.Name;
var jPropertyValue = jProperty.Value;
var renamedObjects = jPropertyValue?.Select(obj => RenamePropertiesByHierarchyName(obj.ToObject<JObject>(), name)).ToList();
foreach (var jObjects in renamedObjects.Select(innerObject => FlattenJsonGetJObjects(innerObject.ToObject<JObject>())))
{
nestedArraysObjectList.AddRange(jObjects);
}
if (fromArrayOfJProperties.Any() && nestedArraysObjectList.Any())
fromArrayOfJProperties = nestedArraysObjectList
.SelectMany(nested => fromArrayOfJProperties, (current, joined) => JoinJObject(joined, current)).ToList();
if (!fromArrayOfJProperties.Any())
fromArrayOfJProperties.AddRange(nestedArraysObjectList);
}
return fromArrayOfJProperties;
}
public static List<JObject> GetJObjectsFromArrayOfValues(List<JProperty> nestedValuesArray)
{
return (from innerArray in nestedValuesArray let name = innerArray.Name let values = innerArray.Value from innerValue in values select new JObject(new JProperty(name, innerValue.ToObject<JValue>()))).ToList();
}
public static JObject RenamePropertiesByHierarchyName(JObject jObject, string hierarchyName)
{
var properties = jObject.Properties().ToList().Select(p => new JProperty($"{hierarchyName}_{p.Name}", p.Value));
return new JObject(properties);
}
public static JObject JoinJObject(JObject parentJObject, JObject innerObject)
{
var joinJObject = new JObject
{
parentJObject.Properties(),
innerObject.Properties()
};
return joinJObject;
}
}