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

Help Studio
, , ,
#1

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

#2

you can deserialize the json and iterate over instead of linq

#3

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.

#4

refer this page may it will be help little bit

#5

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

#6

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

#7

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

#8

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?

#9

“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

#10

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

#11

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

Cheers

#12

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

#13

It worked perfectly. Thanks

#14

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