如何将指定JSON转换为DataTable？使用Newtonsoft.Json遇报错

问题背景 #

给定的JSON数据为列结构（每个键对应一列的所有数据）：

{
    "open": [
                3978,3856,3925,3918,3877.85,3992.7,4033.95,4012,3910,3807,3840,3769.5,3731,3646,3749,
                3770,3827.9,3851,3815.3,3791
            ],
    "high": [
            3978,3925,3929,3923,3977,4043,4041.7,4012,3920,3851.55,3849.65,3809.4,3733.4,3729.8,
            3758,3808,3864,3882.5,3824.7,3831.8
            ],
    "low":  [
            3861,3856,3836.55,3857,3860.05,3962.3,3980,3910.5,3811,3771.1,3740.1,3722.2,3625.1,
            3646,3721.4,3736.4,3800.65,3816.05,3769,3756.15
            ],
    "close": [
            3879.85,3915.9,3859.9,3897.9,3968.15,4019.15,3990.6,3914.65,3826.55,3833.5,3771.35,
            3769.9,3649.25,3690.05,3736.25,3800.65,3856.2,3824.6,3814.9,3779
            ],
    "volume":[
            3937092,1906106,3203744,6684507,3348123,3442604,2389041,3102539,6176776,3112358,     
            3258414,3330501,5718297,3143862,2739393,2105169,1984212,1960538,2307366,1919149
            ],
    "start_Time": [
            1326220200,1326306600,1326393000,1326479400,1326565800,1326825000,1326911400,
            1326997800,1327084200,1327170600,1327429800,1327516200,1327689000,1327775400,
            1328034600,1328121000,1328207400,1328293800,1328380200,1328639400
            ]
}

用户尝试直接反序列化为DataTable的代码报错：

var unwrappedJson = JsonConvert.DeserializeObject<string>(result);
DataTable dataTable = (DataTable)JsonConvert.DeserializeObject(unwrappedJson, (typeof(DataTable)));

错误原因 #

Newtonsoft.Json默认仅支持直接反序列化行结构的JSON（即一个数组，每个元素是包含各列键值对的对象）为DataTable。而当前JSON是列结构（每个键对应一列的所有数据），直接转换会导致解析失败。

解决方案 #

需要先将列结构的JSON转换为行结构的数据，再构建DataTable，以下两种方法均可实现：

方法一：强类型解析+循环构建 #

1. 定义与JSON结构匹配的强类型类：

public class MarketData
{
    public List<double> open { get; set; }
    public List<double> high { get; set; }
    public List<double> low { get; set; }
    public List<double> close { get; set; }
    public List<long> volume { get; set; }
    public List<long> start_Time { get; set; }
}

2. 反序列化并构建DataTable：

// 反序列化JSON到强类型对象
var marketData = JsonConvert.DeserializeObject<MarketData>(result);

// 创建DataTable并定义列
DataTable dataTable = new DataTable();
dataTable.Columns.Add("open", typeof(double));
dataTable.Columns.Add("high", typeof(double));
dataTable.Columns.Add("low", typeof(double));
dataTable.Columns.Add("close", typeof(double));
dataTable.Columns.Add("volume", typeof(long));
dataTable.Columns.Add("start_Time", typeof(long));

// 遍历索引，将对应位置的列值组合为行
int rowCount = marketData.open.Count;
for (int i = 0; i < rowCount; i++)
{
    DataRow row = dataTable.NewRow();
    row["open"] = marketData.open[i];
    row["high"] = marketData.high[i];
    row["low"] = marketData.low[i];
    row["close"] = marketData.close[i];
    row["volume"] = marketData.volume[i];
    row["start_Time"] = marketData.start_Time[i];
    dataTable.Rows.Add(row);
}

方法二：动态解析（无需定义类） #

使用JObject动态读取JSON并构建DataTable，适合结构不固定的场景：

// 解析JSON为JObject
JObject jsonObj = JObject.Parse(result);

// 提取所有列名和对应的数组
var columns = jsonObj.Properties()
    .ToDictionary(p => p.Name, p => p.Value.ToObject<List<object>>());

// 创建DataTable并添加列（自动推断类型）
DataTable dataTable = new DataTable();
foreach (var col in columns)
{
    Type colType = col.Value[0].GetType();
    dataTable.Columns.Add(col.Key, colType);
}

// 构建行数据
int rowCount = columns.First().Value.Count;
for (int i = 0; i < rowCount; i++)
{
    DataRow row = dataTable.NewRow();
    foreach (var col in columns)
    {
        row[col.Key] = col.Value[i];
    }
    dataTable.Rows.Add(row);
}

注意事项 #

• 确保所有数组的长度一致，否则循环时会出现索引越界错误
• 如果JSON中存在null值，需要在赋值时添加空值判断逻辑

内容的提问来源于stack exchange，提问作者Sandip Kodle