LINQ to JSON to get desired result and copy to a data table

Hi,

I have a JSON in the below format

{
“value”:[
{
“Status”: “Failed”,
“SpecificContent”:{
“id”:“abcd”,
“age”:“25”
}
},
{
“Status”: “Successful”,
“SpecificContent”:{
“id”:“efgh”,
“age”:“24”
}
},

]
}

I want to run a LINQ to find all the id and age where the status is “Failed” and copy this data to a data table.

I have tried
JObj(“value”).Where(Function (a) a(“Status”)=“Failed”).Select(Function (b) b(“value”)(“SpecificContent”))

and got error Option strict On disallows implicit conversion from Newtonsoft.Json.Linq.Jtoken to String

I have also tried

(From x In JObj Let a = x(“value”).Select(Function (b) b(“status”)=“Failed”).toArray Select dt.Rows.Add(a)).copyToDataTable

and got error Option Strict on disallows late binding.

Any help on this is highly appreciated.

Thanks in advance

you can deserialize the json and iterate over instead of linq

Hi Vijay,

Thanks for response, but iterating would take significant time as there are more than 150,000 items in the array (Value). Hence trying LINQ. The string is already deserialized in JObj.

refer this page may it will be help little bit

LINQ is still going to iterate over it. LINQ is not faster. That’s a myth.

@Meghdut_Saha You can convert the JSON to data table and filter the status . Can you share that JSON file

The Json is given in the question, it has 2 elements in the value array, but actual data has more than 150,000 elements.

Changing the LINQ to as below

JObj(“value”).Where(Function (a) a(“Status”).ToString=“Failed”).Select(Function (b) b(“value”)).toArray

And assinging it in an array of JToken, the error goes away and it runs successfully.

Now I have a variable jT of type array of JToken which shows in immediate window as JToken[1] { null } but I can not get the values out of it. Am I missing something here?

“value” is the JSON array you filter by “where”
You can not use it in “select”

use something like this
JObj("value").Where(Function(a) (cstr(a("Status")).Equals("Failed"))).Select(Function(b) {cstr(b("SpecificContent")("id")),cstr(b("SpecificContent")("age"))}).toArray

What would be the datatype of the variable to hold this result?

It returns something like Array(of Array(of String))
It should be possible to switch to datatable. But I did not try it…

Cheers

Like this you can get the datatable

dt2 = objJson("value").Where(Function(a) (cstr(a("Status")).Equals("Failed"))).Select(Function(b) dt2.Rows.Add({cstr(b("SpecificContent")("id")),cstr(b("SpecificContent")("age"))})).CopyToDataTable

where dt2 is an existing datatable with mathing structure

cheers

It worked perfectly. Thanks

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.