Link to home
Create AccountLog in
Avatar of Rick Becker
Rick BeckerFlag for United States of America

asked on

Proper JSON Syntax for extracting Data from Nested Arrays

Greetings All..

Ok I am very new to JSON but I the need to use and learn it. I have been able to search the internet to get some help in getting it setup and running on Very Simple JSON files/script. I am Visual Studio 2017 and VB.NET and I  have loaded in the NewtonSoft.Json  libraries which is working very well. However, So far I have only  been working with file/scripts that are simple and a single Array of elements/objects.

The SYNTAX for extracting Data from these elements is pretty easy and pretty straight forward and I am successful in this Simple Data...

However I now have to extraxt some data from some larger files/scripts which contains MULTIPLE Levels of Arrays. These file are 'Customer Order' files that contains an Array of ORDERS each of which can contain an Array of Items.. I need to be able to extract the SKU data from each of the ITEMS on the ORDER'

The following is my current code and below that is a sample of a Single ORDER with the 'Nested' ITEMS;

        Call xmlHttp2.open("GET", sContactURL, False)
        
        xmlHttp2.setRequestHeader("Authorization:", " Basic " + EncodedShipStationID)
        Call xmlHttp2.send()

        'Get the server's response
        sResponse = xmlHttp2.responseText
        sResponse = Trim(sResponse)

        'Save the response  for later evaluation if needed
        RtnVal = VarStringToTextFile(TempDataStorageFolder + "\ShipStationOrderResponse1.xml", sResponse)


       
        Dim json As String = sResponse.ToString
        Dim jsonObject As Newtonsoft.Json.Linq.JObject = Newtonsoft.Json.Linq.JObject.Parse(json)

        '''Dim jsonArray As Newtonsoft.Json.Linq.JArray = jsonObject("items")

        Dim jsonArray As Newtonsoft.Json.Linq.JArray = jsonObject("orders")

        Dim CurrentSKU As String
        Dim CurrentSKU Array() As String

        CurrentSKU = ""

        For Each item As Newtonsoft.Json.Linq.JObject In jsonArray


            CurrentSKU =  CurrentSKU + item.SelectToken("orderNumber").ToString + ","
            
        Next
        CurrentSKUArray = CurrentSKU .Split(",")
        '################################################################
        MsgBox("Done")
        Cursor = Cursors.Default

Open in new window


So my main problem is that I just flat do not know how to reference  'item.SelectToken("Orders.Items.sku")

Any help with syntax for extracting data from the ITEM array/Nested arrays would be most helpful.
Thanks in advance
Rick

The following is a sample file:

ShipStationOrderResponse1.json
ASKER CERTIFIED SOLUTION
Avatar of Zvonko
Zvonko
Flag of North Macedonia image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Here an example how to select tokens by path:
https://www.newtonsoft.com/json/help/html/QueryJsonSelectToken.htm 


Avatar of Rick Becker

ASKER

Hi Zvonko;

WOW ... are you kidding me ... LOL that Worked.. I am flabbergasted...

Thanks that is a combination that I did not try...

Again thanks..

Rick
Thank You Very Much...

Rick
Hi ZVonko... I was wondering if you could take a look a the new question that I posted just a bit ago... you were so very helpful with this one?

"How to Modify a JSON Array without Compromising the format/structure"

If you can that would be great

Rick