Pick only one record from a JSON array in C#

Question

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

Please share the implementation of ConvertJsonToDatatable. — adv12
– adv12, Commented Apr 29, 2022 at 18:07
I can see an array in an array. Can you post json you want pls? — Serge
– Serge, Commented Apr 29, 2022 at 18:12
what is inside ConvertJsonToDatatable() function? are you reading data array separately? — Power Mouse
– Power Mouse, Commented Apr 29, 2022 at 18:29
@adv12 Sure, my mistake I'm sorry I forget it, I edited the original post, thanks — C K
– C K, Commented Apr 29, 2022 at 18:37
@Serge hmm I want to read these kind of values : 1.0562, or even the timestamp inside the "data" part of the JSON — C K
– C K, Commented Apr 29, 2022 at 18:39

Serge · Accepted Answer · 2022-04-29 19:28:50Z

1

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");

edited Apr 29, 2022 at 19:28

answered Apr 29, 2022 at 19:01

Serge

44.4k4 gold badges26 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

C K Over a year ago

I'm sorry to take all your time, but it seems to not be working at all by passing from double to string, no Messagebox even not any "col2"

Serge Over a year ago

@CK I am always testing my code. It is working properly. You have to replace double in 2 places - in a method and in main code

C K Over a year ago

obviously I make a lot of mistakes and that's why we loose time, but actually it seems there is not any other double in my code, I double-checked

Serge Over a year ago

@CK Just copy paste my method again, it is easier

C K Over a year ago

I copy past every time ! the other reply help me to find my way, but I have to thank you for all your dedicated time, you closed hours of work, have a nice week-end !

Power Mouse · Accepted Answer · 2022-04-29 19:05:06Z

0

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

answered Apr 29, 2022 at 19:05

Power Mouse

1,4511 gold badge14 silver badges18 bronze badges

2 Comments

C K Over a year ago

You will never be marked negative by me ! You and the others help me a lot to understand all my mistakes, I will try your code but first of all thanks for your time, I will catch your code and try to show only by example the first element from a row in a MessageBox (so with a string)

C K Over a year ago

Ok the row.Dump wasn't recognized but System.Windows.Forms.MessageBox.Show(row[0].ToString(), "h"); works very well. I have to thank you for all your help and time, you closed hours of work ! have a nice week end

Power Mouse · Accepted Answer · 2022-04-30 20:04:36Z

0

.Dump() is a left over from linqpad. Good tool. Same as console.write

answered Apr 30, 2022 at 20:04

Power Mouse

1,4511 gold badge14 silver badges18 bronze badges

2 Comments

C K Over a year ago

Ok I understand better. Very thanks for all your help and have a nice end of week ! By the way, any idea to have the token before the last token (JArray test = JArray.Parse(token.Last.ToString()); ) I'm trying to get not .Last but just one step before

Power Mouse Over a year ago

row[row.ToArray().Length-2]

Collectives™ on Stack Overflow

Pick only one record from a JSON array in C#

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related