反序列化并将嵌套的JSON API数据导出到CSV [英] Deserialization and Exporting of Nested JSON API Data to CSV

问题描述

我需要从嵌套JSON的API中获取一些数据并将其导出到CSV.在没有创建多个类的情况下，我找不到用于获取该数据以将其导出到CSV的正确语法(有数百个属性，并且可以动态添加新属性).

I have requirement to take some data from an API from nested JSON and export it to CSV. I can't find the right syntax for taking that data to export it to CSV without creating multiple classes (there are hundreds of properties, and new ones can be added dynamically).

API的JSON响应结构如下:

The JSON response structure from the API is below:

{
    "data": [
        {
            "id": "1",
            "type": "Bus",
            "attributes": {
                "property-two": "2020-12-10",
                "property-three": "D",
                "property-four": null,
                "property-five": 5
            }
        },
        {
            "id": "2",
            "type": "Car",
            "attributes": {
                "property-two": "2020-12-10",
                "property-three": "D",
                "property-four": null,
                "property-five": 5
            }
        }
    ]
}

我们只需要导出属性"每个数据集中的节点转换为CSV，但似乎无法展平数据或仅提取这些节点.

We only need to export the "attributes" node from each dataset to CSV, but cannot seem to flatten the data or extract just those nodes.

以下代码获取 JToken 对象的列表，但是我不确定如何可以将其导出到CSV，而无需再次进行重新序列化和反序列化.数据类型是动态的，因为可以在其中添加和删除列.

The following code gets a list of JToken objects, but I'm not sure how this can be exported to CSV without re-serializing and de-serializing again. The datatype is dynamic since columns can be added and removed from it.

var jsonObject = JObject.Parse(apiResponseString);
var items = jsonObject["data"].Children()["attributes"].ToList();
//TODO: Export to CSV, DataTable etc

是否可以仅反序列化来自属性节点的数据，以及如何进行处理(无论是在初始的 JObject.Parse 上还是再次序列化 JToken 列表))?我与 JObject 无关，因此也可以使用Newtonsoft或其他工具.

Is it possible to deserialize the data from the attributes nodes only, and how would that be done (whether on the initial JObject.Parse or serializing the JToken list again)? I'm not tied to JObject, so can use Newtonsoft or other as well.

推荐答案

使用Json.Net的

Using Json.Net's LINQ-to-JSON API (JObjects), you can convert your JSON data to CSV as shown below.

首先定义几个简短的扩展方法:

First define a couple of short extension methods:

using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json.Linq;

public static class JsonHelper
{
    public static string ToCsv(this IEnumerable<JObject> items, bool includeHeaders = true)
    {
        if (!items.Any()) return string.Empty;
        var rows = new List<string>();
        if (includeHeaders)
        {
            rows.Add(items.First().Properties().Select(p => p.Name).ToCsv());
        }
        rows.AddRange(items.Select(jo => 
            jo.Properties().Select(p => p.Value.Type == JTokenType.Null ? null : p.Value).ToCsv()
        ));
        return string.Join(Environment.NewLine, rows);
    }

    public static string ToCsv(this IEnumerable<object> values)
    {
        const string quote = "\"";
        const string doubleQuote = "\"\"";
        return string.Join(",", values.Select(v => 
            v != null ? string.Concat(quote, v.ToString().Replace(quote, doubleQuote), quote) : string.Empty
        ));
    }
}

然后您可以执行以下操作:

Then you can do:

var obj = JObject.Parse(json);
var csv = obj.SelectTokens("..attributes").Cast<JObject>().ToCsv();

这是一个有效的演示: https://dotnetfiddle.net/NF2G2l

Here is a working demo: https://dotnetfiddle.net/NF2G2l

这篇关于反序列化并将嵌套的JSON API数据导出到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！