I'm trying to extract a record from a JSON array but it doesn't seem to work. Here my code :

 DataTable table = ConvertJsonToDatatable(responseBody);
 System.Windows.Forms.MessageBox.Show(table.Columns[1].ToString(), "transformation");

The MessageBox isn't showing. I have checked responseBody and the variable isn't empty at all. Here the structure of this variable (and the JSON array rear)

{
"data": 
    [
        [
          1651217520000,
          1.0562,
          1.0562,
          1.056,
          1.0561,
          0,
          0
        ],
        [
          1651217580000,
          1.0561,
          1.0563,
          1.0561,
          1.0561,
          0,
          0
        ]
    ],
          
"events": null
}

public static DataTable ConvertJsonToDatatable(string jsonString)
        {
            var jsonLinq = JObject.Parse(jsonString);
            // Find the first array using Linq
            var linqArray = jsonLinq.Descendants().Where(x => x is JArray).First();
            var jsonArray = new JArray();
            foreach (JObject row in linqArray.Children<JObject>())
            {
                var createRow = new JObject();
                foreach (JProperty column in row.Properties())
                {
                    // Only include JValue types
                    if (column.Value is JValue)
                    {
                        createRow.Add(column.Name, column.Value);
                    }
                }
                jsonArray.Add(createRow);
            }
            return JsonConvert.DeserializeObject<DataTable>(jsonArray.ToString());
        }

Does anyone have an idea of how to extract/pick one value from this array (which is a string in my code) ?

Have a nice week end everyone and thanks in advance

CodePudding user response：

i probably would be marked negative, but i try to explain how it looks like inside. i made example to show tow to get back list of arrays it might visualize for you.

void Main()
{
    string json = "{\"data\":[[1651217520000,1.0562,1.0562,1.056,1.0561,0,0],[1651217580000,1.0561,1.0563,1.0561,1.0561,0,0]],\"events\":null}";
    var obj = JObject.Parse(json);
    foreach (JToken token in obj.FindTokens("data"))
    {
        foreach (JArray row in JArray.Parse(token.ToString()))
        {
            row.Dump();
            row[0].Dump("first element");
            
        } 
    }
    
}

public static class JsonExtensions
{
    public static List<JToken> FindTokens(this JToken containerToken, string name)
    {
        List<JToken> matches = new List<JToken>();
        FindTokens(containerToken, name, matches);
        return matches;
    }

    private static void FindTokens(JToken containerToken, string name, List<JToken> matches)
    {
        if (containerToken.Type == JTokenType.Object)
        {
            foreach (JProperty child in containerToken.Children<JProperty>())
            {
                if (child.Name == name)
                {
                    matches.Add(child.Value);
                }
                FindTokens(child.Value, name, matches);
            }
        }
        else if (containerToken.Type == JTokenType.Array)
        {
            foreach (JToken child in containerToken.Children())
            {
                FindTokens(child, name, matches);
            }
        }
    }
}

result would be an array of jarray

so you can build you DataTable rows

CodePudding user response：

you have to fix table creating code

public static DataTable ConvertJsonToDatatable(string jsonString)
{
    var jsonLinq = JObject.Parse(jsonString);
    // Find the first array using Linq
    var linqArray = jsonLinq.Descendants().Where(x => x is JArray).First();

     //or maybe this would be enough
    var  linqArray = JObject.Parse(jsonString)["data"];

    var jsonArray = new JArray();
    foreach (var row in (JArray)linqArray)
    {
        var createdRow = new JObject();
        var i = 0;
        foreach (var item in row)
        {
            i  ;
            createdRow.Add("col"   i.ToString(), (string)item);
        }
        jsonArray.Add(createdRow);
    }
    return jsonArray.ToObject<DataTable>();
}

how to use

DataTable table = ConvertJsonToDatatable(responseBody);
string val = table.Rows[0].Field<string>("col2");
System.Windows.Forms.MessageBox.Show(val, "transformation");