[RESOLVED] Simple JSON table extract needed

[cory_jackson]

I have a HTTP response which is a single row JSON table. I could use RegEx to get what I want, but I want to do it 'proper'. I've been searching for an hour now and I'm only more confused. I read that there is JSON support in the latest version of .NET but I found System.JSON and someone here said that was a Silverlight thing and not useful. So I also see a JSON serializer/deserializer, but that seems to be for JSON objects. I know there's the third party JSON.NET but I want to use MS components only. I was certain I saw it here before but I can't find it now. And there is so much online it's like looking for a needle in a haystack. I just can't think f a good term. Can someone point me in the direction of a simple explanation and possibly example?

[Shaggy Hiker]

There is a fairly simple way to do it, but it may take a bit of debugging to get it quite right. Here's a bit of code:

Dim js As New System.Web.Script.Serialization.JavaScriptSerializer()
Dim res = js.Deserialize(Of Dictionary(Of String, Object))(surv)

In that code, "surv" is a string of JSON. Not an actual JSON object, but a stringified JSON object, which is what you will be getting from HTTP. What "res" ends up being is a Dictionary(of string, object). Each key is one of the members of the JSON object, with the value being the value from the JSON object. What that value is can get varied, but it's just a string. So, if the value is an integer, you can convert it to an integer (but use TryParse, in case it is just ""), if it's a string you can just use .ToString. However, if it's an array or JSON object, then it will take further parsing. That doesn't sound like it's the case for you.

[cory_jackson]

I'm on it! Thanks!

[cory_jackson]

That namespace isn't working in my project. I can go to System.Web but I don't see "Script" as a member. I tried searching the references to add it but I don't see it. How do I get System.Web.Script.Serialization.JavaScriptSerializer()?

[cory_jackson]

Never mind. I needed to add System.Web.Extensions.

[cory_jackson]

New problem. There appear to be two things. One is some header information containing containing descriptive information like the number of records returned. Which is useful;l, I need that also. So your code is returning these value and not the values in the table. How do I get to the next table (or whatever it's called)?

[cory_jackson]

The JSON I'm getting seems to be a hybrid. It seems to be an object and one of the entities within is a collection or records. IE Tabular data. But the table isn't simple (flat) either. Instead of columns each containing a string value, some of the columns contain other groups of elements. For instance address contains line1, line2, line3, city, state, ZIP. I feel like I should be able to load this into some kind of a JSON object and drill down though the hierarchy. Can serialization do that? Maybe i need to do multiple levels of serialization. I'll try that next.

[Sitten Spynne]

JSON in .NET is a mess. MS spent a lot of money and effort on developing XML parsers, but the rest of the world decided XML was too complex and created JSON in response. It took MS a while to begrudgingly add a JSON parser, and it's buried in weird places.

I'll start with the simple answer: none of the Microsoft JSON parsers are very popular. Almost every professional .NET developer uses the Newtonsoft JSON package instead. Let's talk about the MS ones briefly.

The System.JSON namespace is basically not available for projects. It is part of the Silverlight profile and you'll have to do a lot of silly things to make it work in a WinForms application. Don't bother.

The "official" .NET JSON serializer is System.Runtime.Serialization.Json.DataContractJsonSerializer. This is widely available in .NET profiles, basically "anything since .NET 3.5". Many people use it, but until recent .NET versions it's had some idiocyncracies that meant it wasn't 100% compatible with the rest of the world's JSON serializers. I don't know if that's still true or not. Most people made their choice long ago and stuck with Newtonsoft.JSON. If you want to use it, maybe you can talk someone else into a tutorial.

Newtonsoft JSON is what most professionals use. Third-party packages aren't scary, it's OK to use software that's not written by Microsoft.

There are a few ways to deserialize JSON with this library.

One is so unweildly no one tends to do it. It iterates over every token of the JSON file and you sort of build your objects as it goes. I guess if your files were multiple GB in size this would be the best way to fool with them, but really no one does this.

That leaves us with two more ways. They both do more or less the same thing. Let's make sure we understand JSON first. It's just a way to represent "an object". It comes from JavaScript, so it carries an interesting fact with it. In JS, "objects" and "dictionaries" are synonymous. In JavaScript, arrays and dictionaries don't have to hold all the same type of data. That's how this works. JSON libraries in .NET try to maintain these facts, but it gets sort of ugly because .NET is not JavaScript. In .NET, arrays and dictionaries MUST contain homogenous data. And in .NET, there is NOT an easy way to go from a Dictionary to a particular class. This leads to what I think is the fatal flaw in the way I tend to see people say is "easy" JSON deserialization.

So let's actually work with JSON. This JSON object has basically every possible situation you need to worry about it, so it's a good example:

{
    "string": "Hello",
    "number": 1,
    "array": [1, 2, 3],
    "otherObject": {
        "number": 10
    }
}

JSON objects can have numbers, strings, arrays, or other objects inside. This JSON object has one of each of those things. So if we can figure out how to parse it, we'll know how to use the library. So let's compare and contrast the two techniques.

JSON -> Dictionaries
I don't like this method, but a lot of people seem to choose it on these forums. I think they do this because it looks easy. I don't think it's much easier, and I think you have to learn more to be successful with it. Let's get it out of the way. Here's a program that reads the object and prints all of its values:

Imports System.IO
Imports Newtonsoft.Json
Imports Newtonsoft.Json.Linq

Module Module1

    Sub Main()
        Dim json = File.ReadAllText("data.txt")

        Dim parsed = JsonConvert.DeserializeObject(Of Dictionary(Of String, Object))(json)

        Dim stringValue As String = parsed("string").ToString()
        Dim numberValue As Integer = CInt(parsed("number"))
        Dim arrayValue As JArray = parsed("array")
        Dim subObject As JObject = parsed("otherObject")

        Console.WriteLine("string: {0}", stringValue)
        Console.WriteLine("number: {0}", numberValue)

        Console.WriteLine("Array values:")
        For Each item As Object In arrayValue
            Console.WriteLine(item)
        Next

        Console.WriteLine("otherObject.number:")
        Console.WriteLine(CInt(subObject("number")))

    End Sub

End Module

It's straightforward until you get to arrays and objects. They come back as JArray and JObject items. JObject is sort of like a Dictionary in and of itself. JArray spits out its values as Object.

Really the main reason I hate this technique is everything comes out as Object, and I have to remember what to cast everything to. It usually takes me a few tries to get it right.

JSON -> Classes
This technique involves writing a class for each kind of JSON object in the JSON. Each class should have a property that the JSON indicates. The code is easier to understand than those sentences:

Imports System.IO
Imports Newtonsoft.Json
Imports Newtonsoft.Json.Linq

Module Module1

    Sub Main()
        Dim json = File.ReadAllText("data.txt")

        Dim parsed = JsonConvert.DeserializeObject(Of ExampleData)(json)

        Console.WriteLine("number: {0}", parsed.Number)
        Console.WriteLine("string: {0}", parsed.String)

        Console.WriteLine("Array values:")
        For Each item As Integer In parsed.Array
            Console.WriteLine(item)
        Next

        Console.WriteLine("OtherObject.Number: {0}", parsed.OtherObject.Number)

    End Sub

End Module

Public Class ExampleData

    Public Property Number As Integer
    Public Property [String] As String
    Public Property Array As Integer()
    Public Property OtherObject As OtherObject

End Class

Public Class OtherObject

    Public Property Number As Integer

End Class

This does the same thing as the previous code, but I find it far less complicated since I don't have to think about casting or accessing dictionary elements.

Which technique you use is up to you, but those are the two sane ways to use the Newtonsoft library. There are lots of ways to enable options that do fancy things, but you can read the documentation to find those.

[Sitten Spynne]

If your JSON is weird, post a sample file and we can verify if it looks like something strange. Odds are you just need to build an object hierarchy.

[cory_jackson]

Sitten Spynne: Thank you for that practical edification on what programmers actually use and do. Also for the edification of how JSON is conceptually different. That helps me enormously. I think I understand your examples. At least I understand your concepts. I'll need to do some experimentation and see if the client is OK with a third party library.

[cory_jackson]

Shaggy Hiker: I like what you have shown me. I misunderstood the term "dictionary". To me a dictionary was a simple way to lookup strings that I rarely used. Now I need to re-educate myself and try to understand. But I wrote a sample with your code and I see the items I want in there. Can you give me a simple example how to drill down into a multi-level hierarchy like Sitten Spynne's example? I'll experiment myself after lunch.

[Sitten Spynne]

A Dictionary is one of many names for a particular kind of data structure. Some languages call them "hash tables", others call them "associative arrays". There's reasons no one can really agree on a name but they aren't important. The .NET name for them is "Dictionary" so let's stick with that.

Dictionaries are like arrays, but fancier. In an array, the index is always an Integer. The index always starts at 0, and elements are always numbered consecutively. That is good for many problems, but sometimes it's not convenient to use 0-n as your indexing scheme. For example, you may have a list of buckets, each a different color, and you want to refer to them by that color. This is the problem a Dictionary helps solve.

The "index" in a dictionary can be any type. It doesn't need to be contiguous, it just needs to have a way for the = operator to work sensibly with it. It's very common for the index of a Dictionary to be a String. Like an array, the "items" can be any type, including arrays and other dictionaries.

Since Dictionary is a generic type in .NET, it's name is really Dictionary(Of TIndex, TValue). That means when you create one, you have to pick the type of the index and the type of the value. Here's some example code that sets up a dictionary with the results of a hot-dog eating contest, then uses that dictionary to display how many hot dogs one person ate:

Dim nameToResults As New Dictionary(Of String, Integer)()
nameToResults("Alice") = 10
nameToResults("Bob") = 9
nameToResults("Cookie Monster") = 0

Console.WriteLine("And the number of hot dogs Cookie Monster ate is...")
Console.WriteLine(nameToResults("Cookie Monster"))

As you can see, the syntax is very similar to an array, but now we're using Strings instead of Integers as the index.

These sort of relate to JSON naturally. In JavaScript, all objects are technically Dictionaries. So if you have a Customer 'class' with a "name" property, you can use both of these syntaxes to get the name:

var customerName = customer.name

var alsoName = customer["name"]

That's how JSON came about. It's a syntax that started its life as "I'm lazy and want a shortcut for creating JavaScript objects." It turned out so simple compared to XML people decided to use it just about everywhere.

But because dictionaries and objects are "the same thing" in JavaScript, that means you can stash a ton of different things in a JavaScript dictionary:

var customer = { }
customer["id"] = 10
customer["name"] = "Joe Cool"
customer["nicknames"] = ["Joe", "JC"]

.NET doesn't let you do that with its Dictionaries unless the "TValue" type is Object. But when you make a Dictionary like that, every time you take an item out you have to worry about verifying it's the type you expect. (You have to do this in JavaScript, too, but the language is a lot less picky about it.)

I imagine the type Shaggy Hiker pointed out works very similarly to what I showed. You can find examples in the documentation. But I'm not sure how much I trust it since Microsoft decided to lead with:

Json.NET should be used serialization and deserialization.

Bad grammar aside, it's not a good sign when MS says "You should use something else."

[Shaggy Hiker]

If you can rely on the structure of the JSON, then what I posted works fine. If you can't rely on the structure of the JSON, then that solution is not a good one. Therefore, I have used that for things where I created both ends (and one other case where I didn't create the receiver, but have little to fear from it changing). Sitten showed some examples of things nested within other things where it gets tedious to unpack the JSON string using that technique, but when the object that was stringified doesn't belong as an object on the server side, then a dictionary is an excellent alternative.

Dictionaries are really useful. It's a container that associates a unique key with a value (which doesn't have to be unique). From one test that I did, it appears that retrieving an item from a dictionary based on the key is faster than retrieving an item from a List or array, which likely means that MS took advantage of the unique nature of the key to perform a kind of binary search....or not, I've never gone looking.

Because the only restriction on the dictionary key is that it has to be unique, you can make some truly exotic dictionaries. I know I have a dictionary where the key is a datarow, and I have at least one place where there is a Dictionary(of IForgetWhatType, Dictionary(of String, List(of SomeClass))). Because of the speed of looking things up by key, there are times when I've had both a Dictionary and a reverse Dictionary, though in such a case, both the key and the value have to be unique.

Dictionaries can be just a dictionary, but they can be so much more.

[Shaggy Hiker]

Shaggy Hiker: I like what you have shown me. I misunderstood the term "dictionary". To me a dictionary was a simple way to lookup strings that I rarely used. Now I need to re-educate myself and try to understand. But I wrote a sample with your code and I see the items I want in there. Can you give me a simple example how to drill down into a multi-level hierarchy like Sitten Spynne's example? I'll experiment myself after lunch.

That's where it tends to take a bit of debugging. Some returns from APIs can have several elements, and you may want only element N, in which case you do get to drill down. However, the easiest thing I have found is just to put a breakpoint in there and see what you get.

Here's an example of the results of that:

                    Dim js As New System.Web.Script.Serialization.JavaScriptSerializer()
                    Dim rawdata = js.Deserialize(Of Dictionary(Of String, Dictionary(Of String, Object)))(result)
                    Dim innerDict = rawdata(rawdata.Keys(0))
                    If innerDict.Keys(1) = "record_values" Then
                        Dim subInner As Dictionary(Of String, Object) = CType(innerDict(innerDict.Keys(1)), Dictionary(Of String, Object))
                        For Each ky In subInner.Keys
                            sb.Append(ky & ": " & subInner(ky).ToString & Environment.NewLine)
                        Next
                    Else
                        sb.AppendLine("A non-standard error was found")
                    End If

The first few lines are similar to my first example (different variable names, though). However, upon examining rawdata I dound that it was a JSON object in a JSON object, so it creates a Dictionary(of String, Dictionary(of String,Object)). In other words, the outer dictionary held various things, but all I really wanted was the first item, which was a JSON object, so it's turned into a Dictionary (of String, Object). I then get the second item from that to see if that is "record_values", in which case I cast the inner Object to a Dictionary( of String, Object). So, the original string was actually a JSON object containing a JSON object as the first element, which contained a JSON object as it's second element.

[cory_jackson]

This has been very enlightening. Thank you very much Sitten. I want to try Shaggy's solution first. Please don't think that means I am ungrateful or that I feel your suggestion is bad. But in this case I can count on the structure always being the same and, as Shaggy pointed out, it's all stringified. Now i need to do some experiments to make this work. Before I get into that I had one odd question about types. I'm looking at the data and I don't see anything in there that defines the data types. They just look like dictionaries of dictionaries of string. Since it's naively string, can't all of the items be handled as string type? My built in destination object accepts strings even when the ultimate and internal data type is something different like Decimal.

[szlamany]

Can you post your JSON string please?

[cory_jackson]

I wish I could, but i can't. I will see if I can make a dummy sample quick.

[cory_jackson]

Forget my question about type. I see now how they typing works.

[cory_jackson]

I tried to pare this down and replace data but I think I have corrupted it. Unbalances the braces someplace I'm guessing. Is this good enough to demonstrate what I' working with? I see how I can drill into the first level using the index numbers but I haven't figured out how to use the key to 'search' yet. Working on that. Also not sure there is another section with rows of data. (Records). Trying to figure that all out. Shaggy's object has it al there. I can see it in the debugger. But I'm trying to learn the syntax to address it. I'll get it. I just need to experiment a little.

{
   "SearchToken": "3BAFD8FFB4D15B07654CA1BB0C782791",
   "TotalRecords": 1,
   "Records": [
      {
         "OwnerID": "379314",
         "TID": "1703465760",
         "OwnerName1": "Fred Jackson",
         "OwnerName2": null,
         "OwnerAddress": {
            "Line1": "",
            "Line2": "804 WILLIAMS ST",
            "Line3": "",
            "City": "COPPEROPOLIS",
            "State": "MT",
            "Zip": "59999"
         }]
   }
}

[dday9]

I have a codebank contribution that converts JSON literals into XDocuments here: http://www.vbforums.com/showthread.p...=1#post5200193

The reason why this is important is that, as Sitten Spynne pointed out, MS invested a lot of time into XML and so doing any kind of queries or manipulations would be much easier using the built-in .NET XML classes. Using your sample JSON, take a look at this example that utilizes the code provided in the link above (by the way the JSON was invalid, move the closing ']' outside the second to last closing '}'): Fiddle

[szlamany]

You should be able to use Shaggy's initial post about the dictionary to break that up.

The problem you will have is that the RECORD item in the dictionary is an ARRAY.

You need to take that object and use the same shaggy code just not into a dictionary - instead into an IList(Of Object) type since the []'s indicate an ARRAY in JSON.

Now once you have that array of OBJECT's you would once again use the shaggy code for EACH element of the array to look at the OBJECT - note that {} indicates a key-value pair object...

[cory_jackson]

szlamany: I think I understand. I was thinking it had to be something like that. I was hoping one could drill all the way down but it wasn't working. Enumerating makes sense. Like onion layers. Cool. That makes me hungry. Which reminds me, I need to prep dinner!

[PlausiblyDamp]

You could use something like https://jsonutils.com/ to try and generate the appropriate VB classes from the JSON...

It suggested

Public Class OwnerAddress
        Public Property Line1 As String
        Public Property Line2 As String
        Public Property Line3 As String
        Public Property City As String
        Public Property State As String
        Public Property Zip As String
    End Class

    Public Class Record
        Public Property OwnerID As String
        Public Property TID As String
        Public Property OwnerName1 As String
        Public Property OwnerName2 As Object
        Public Property OwnerAddress As OwnerAddress
    End Class

    Public Class Example
        Public Property SearchToken As String
        Public Property TotalRecords As Integer
        Public Property Records As Record()
    End Class

Although the class names might be a bit off and it has OwnerName2 as Object, you could use that with the code Sitten posted in post #8 - it should happily parse the JSON into the classes for you.

[Sitten Spynne]

There are a lot of complex solutions for a very simple problem here. PlausiblyDamp is on the right track, and the only thing easier to query than XML is plain old object. It's Halloween. Take a chance with the "Class" keyword. The Spooky VB Developer Hell Dream only lasts but a night.

[NeedSomeAnswers]

I'll start with the simple answer: none of the Microsoft JSON parsers are very popular. Almost every professional .NET developer uses the Newtonsoft JSON package instead. Let's talk about the MS ones briefly.

I would second this, i tried the MS Json parsers, but they often didn't seem to deal with my json properly if it was even slightly complex, either i would get extra characters or header info i didn't want coming through in the de-serialised string.

NewtsonSoft.Json is available from NuGet and in my experience is just a much much superior JSon parser then the MS ones, also the code syntax for using it just feels nicer.

[Shaggy Hiker]

There are a lot of complex solutions for a very simple problem here. PlausiblyDamp is on the right track, and the only thing easier to query than XML is plain old object. It's Halloween. Take a chance with the "Class" keyword. The Spooky VB Developer Hell Dream only lasts but a night.

Hittin' the sauce???

[Sitten Spynne]

Hittin' the sauce???

Grouchy and fatigued, in ways I can't think of a friendly way to explain in public.

[cory_jackson]

szlamany: You're right, It appears as a System.Collections.ArrayList. I ashamed I didn't see that before.
VS is amazing. It actually lets me drill down into that array and see the items and values. Fantastic. I should learn how to properly use this program someday.

[ChrisE]

You should be able to use Shaggy's initial post about the dictionary to break that up.

The problem you will have is that the RECORD item in the dictionary is an ARRAY.

You need to take that object and use the same shaggy code just not into a dictionary - instead into an IList(Of Object) type since the []'s indicate an ARRAY in JSON.

Now once you have that array of OBJECT's you would once again use the shaggy code for EACH element of the array to look at the OBJECT - note that {} indicates a key-value pair object...

Hi sz,

I am new to .Net so correct me if I'm wrong.
so JSON Objects become dictionaries (IDictionary)
and JSON arrays [...] become lists (IList)

some pseudo Code..

Dim myFirends As IList = oJsonObject("myFirends")

Dim myFriend As IDictionary = myFirends(0)

For Each Contact In myFriend("Contact")
  Console.WriteLine(Contact("firstName"))
  Console.WriteLine(Contact("lastName"))
  ' etc.
End

regards
Chris

[Sitten Spynne]

I caught up on my work and decided to revisit this.

I really don't think the dictionary approach is a good one. It certainly works, but it's not super fun to have to hack out a few bits, use the debugger to divine what the parser is doing, then hack out a few more bits. I'm going to make one final case for the object-based approach, including a demonstration of how to search/query for results when you have multiple results. I really think it's a more straightforward approach, since you know what your data looks like from start to finish.

I'm going to build off PlausiblyDamp's objects, because they are correct and I don't see any need to make a major change. There is one slight tweak in that instead of OwnerName2 being an Object, I'm making it a String on the assumption it's a String like the last one. "null" is a valid value for String but your example JSON leaves a tiny chance that OwnerName2 could be some other object, since "null" is also a valid value for objects.

I used this as the data file so we'd have 3 results for searching:

{
   "SearchToken": "3BAFD8FFB4D15B07654CA1BB0C782791",
   "TotalRecords": 3,
   "Records": [
      {
         "OwnerID": "379314",
         "TID": "1703465760",
         "OwnerName1": "Fred Jackson",
         "OwnerName2": null,
         "OwnerAddress": {
            "Line1": "",
            "Line2": "804 WILLIAMS ST",
            "Line3": "",
            "City": "COPPEROPOLIS",
            "State": "MT",
            "Zip": "59999"
         }
   },
   {
        "OwnerID": "12345",
        "TID": "111111111",
        "OwnerName1": "Jack Aardvark",
        "OwnerName2": null,
        "OwnerAddress": {
        "Line1": "",
        "Line2": "805 WILLIAMS ST",
        "Line3": "",
        "City": "COPPEROPOLIST",
        "State": "MI",
        "Zip": "59889"
        }
   },
   {
         "OwnerID": "134567",
         "TID": "2222222222",
         "OwnerName1": "Son Sonjack",
         "OwnerName2": "Maybe",
         "OwnerAddress": {
            "Line1": "",
            "Line2": "804 JOYSTICK ST",
            "Line3": "",
            "City": "BRONZEOPOLIS",
            "State": "TX",
            "Zip": "57999"
         }
   }]
}

Here's an application that parses the JSON and demonstrates some queries.

Imports System.IO
Imports Newtonsoft.Json

Module Module1

    Sub Main()
        Dim json = File.ReadAllText("data.txt")

        Dim parsedResults = JsonConvert.DeserializeObject(Of Example)(json)

        Console.WriteLine("I found {0} results.", parsedResults.TotalRecords)

        ' "Find people with 'Jackson' in their name":
        Dim jacksons = From record In parsedResults.Records
                       Where record.OwnerName1.Contains("Jackson")
                       Select record
        Console.WriteLine("There are {0} Jacksons.", jacksons.Count())

        ' "Find Fred Jackson, using lambda syntax."
        Dim fredJackson = parsedResults.Records _
            .Where(Function(r) r.OwnerName1 = "Fred Jackson")
        Console.WriteLine("I found {0} Fred Jackson(s).", fredJackson.Count())

        ' "Sort customers by last name, or just the name if there is no last name."
        Dim sorted = From record In parsedResults.Records
                     Let nameTokens = record.OwnerName1.Split()
                     Let sortKey = If(nameTokens.Length = 2, nameTokens(1), record.OwnerName1)
                     Order By sortKey
                     Select record
        Console.WriteLine("Names, sorted by last name:")
        For Each item In sorted
            Console.WriteLine(item.OwnerName1)
        Next
    End Sub

End Module

Public Class OwnerAddress
    Public Property Line1 As String
    Public Property Line2 As String
    Public Property Line3 As String
    Public Property City As String
    Public Property State As String
    Public Property Zip As String
End Class

Public Class Record
    Public Property OwnerID As String
    Public Property TID As String
    Public Property OwnerName1 As String
    Public Property OwnerName2 As Object
    Public Property OwnerAddress As OwnerAddress
End Class

Public Class Example
    Public Property SearchToken As String
    Public Property TotalRecords As Integer
    Public Property Records As Record()
End Class

Pulling these tasks off with a Dictionary-based approach is going to take a ton more effort, considering all the casts and conversions you'll have to do.

[cory_jackson]

I think I understand it all now. I'm able to drill down into the ArrayList easily. There are more array list within but I can drill down though them as well. I thought I would need to serialize again but i see the serializer goes down into every level in one go. Very cool.

Dim jsMain As New System.Web.Script.Serialization.JavaScriptSerializer()
Dim dictOuter = jsMain.Deserialize(Of Dictionary(Of String, Object))(System.IO.File.ReadAllText("A:\Sample.json"))
Dim strOuterValue0 As String = dictOuter.Values(0).ToString 'Gets the string directly. 
Dim intRecordCount As Integer = CInt(dictOuter.Item("TotalRecords")) 'Get the value by the key string
Dim objRecords As ArrayList = CType(dictOuter.Values(2), ArrayList)
Dim dictRecord As Dictionary(Of String, Object) = CType(objRecords(0), Dictionary(Of String, Object))
Dim strRecord0Value2 As String = dictRecord.Values(2).ToString 'Third value in the dictionary.

[szlamany]

Ok that makes more sense. Just one deserialize puts it all out in a ready format. You can almost detect the datatypes programatically and write your own crawler.

[Sitten Spynne]

Yes, it would certainly be useful if someone wrote a library that converted the difficult-to-navigate IList/IDictionary hybrids into an object model for you.

Maybe you could build one starting with dday9's JSON-to-XML code. With the XML in hand, you could import the data into MSSQL. From there, it'd be easy-peasy to convert it to objects, so long as you build an Entity Framework object model!

[szlamany]

Here is some code that does kind of what you want that I wrote some time ago. It works against an application pool on my back end web server and it's returning really what amounts to debug information to me...

I would need to be more recursive to work against any old dictionary...

strMessage = ""
With Application
    .Lock()
    For i As Integer = 0 To .Count - 1
        strMessage &= .Keys(i) & "="
        If TypeOf .Item(i) Is String Then
            strMessage &= .Item(i).ToString()
        ElseIf TypeOf .Item(i) Is Dictionary(Of String, Dictionary(Of String, String)) Then
            Dim dl As Dictionary(Of String, Dictionary(Of String, String)) = DirectCast(.Item(i), Dictionary(Of String, Dictionary(Of String, String)))
            For j As Integer = 0 To dl.Count - 1
                strMessage &= "<br/ >..." & dl.Keys(j) & "<br/ >"
                Dim dl2 As Dictionary(Of String, String) = DirectCast(dl.Values(j), Dictionary(Of String, String))
                For k = 0 To dl2.Count - 1
                    strMessage &= "...\..." & dl2.Keys(k) & "=" & dl2.Values(k).ToString()
                    strMessage &= "<br />"
                Next
            Next
            strMessage &= "<br />"
        ElseIf TypeOf .Item(i) Is Dictionary(Of String, List(Of String)) Then
            Dim dl As Dictionary(Of String, List(Of String)) = DirectCast(.Item(i), Dictionary(Of String, List(Of String)))
            For j As Integer = 0 To dl.Count - 1
                strMessage &= "<br/ >..." & dl.Keys(j) & "=" ' & dl.Values(j) & "<br/ >"
                For k As Integer = 0 To dl.Values(j).Count - 1
                    If k > 0 Then strMessage &= ", "
                    strMessage &= dl.Values(j)(k).ToString()
                Next
            Next
        Else
            strMessage &= .Item(i).ToString()
        End If
        strMessage &= "<br />"
    Next
    .UnLock()
End With

[cory_jackson]

I ended up using Shaggy's class. It's simple and easy for me to understand and I don't need to import a third party class. In this case I know what I'm going to get in advance, though I still verify, and I don't need to plow though a bunch of items. In this case it only returns one record. And there's an item for record count that I can use. Anything other than 1 and I bail. If it was more complex and more unknown I can see using the JSON.NET or others.
The help you all gave me was amazing. Sitten I didn't use your method but your posts were the most valuable in therms of understanding it all. I learned a lot here and I greatly appreciate all your help and teaching. Wonderful experience.

[szlamany]

Hi sz,

I am new to .Net so correct me if I'm wrong.
so JSON Objects become dictionaries (IDictionary)
and JSON arrays [...] become lists (IList)
...
regards
Chris

In case you did not see that this was in fact that way it worked - yes - I will tell you those are the types used.

fwiw, for all the hemming and hawing here about JSON - I've been using MS against JSON AJAX calls for 6 years now and never had a problem...

[NeedSomeAnswers]

Glad you were able to get a resolution but i do wonder why you have this position -

I know there's the third party JSON.NET but I want to use MS components only.

I don't need to import a third party class

Why dont you like importing a third party class?

when you use an MS function like their serializer you are importing a class, just an MS one. Importing a class / components from a reputable third part is really no different, and your limiting yourself by making a lot of really good components created by third parties unavailable to you.

I wonder is it just that you trust MS classes and not others you dont know? or is there some other reason you only want to use MS components?

[cory_jackson]

I have never imported one before so it's another thing to learn how to do and how to maintain is one reason. But the main reason is K.I.S.S. If I have an wrench in my bicycle toolkit, I'll try to learn how to use it first. If it works then I don't need to buy a ratchet and sockets. Sure the ratchet and socket set is more capable, but I don't need to lug them around in my toolkit if I only need to remove a single nut.
I need to come back here and look at Sitten's code. Now that I know how this works, I want to see how JSON.NET will make it simpler. What I have seems pretty simple to me so I can't imagine how it could be better. But i'm sure there must be a good reason so I need to consider that. And I can definitely see how with more complex challenges could take advantage of a third party solution. But for my case it seems MS has it pretty well solved. No need to go to the neighbor to borrow milk when i have some in the fridge.

[szlamany]

@cory_jackson - analogy king!

[cory_jackson]

I have been criticized for using too many analogies. :-)