Parse JSON with Array

Thomas_Mueller · March 9, 2016, 10:56am

I have a JSON - File with an Array, and have problems to find the right way to loop through the array.
I downloaded the “XOJO User Guide Book 3 Framework” in the Apple iBook Store, because of the JSON Chapter. But I didn’t get it, how to parse Arrays in JSONs.

My JSON is this one:

[code]{
“d”: {
“results”: [12]
0: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2015’,Month=‘04’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2015’,Month=‘04’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2015”
“Month”: “04”
“AverageDaysInArrears”: “-2”
}-
1: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2015’,Month=‘02’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2015’,Month=‘02’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2015”
“Month”: “02”
“AverageDaysInArrears”: “-3”
}-
2: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2015’,Month=‘01’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2015’,Month=‘01’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2015”
“Month”: “01”
“AverageDaysInArrears”: “-5”
}-
3: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘12’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘12’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “12”
“AverageDaysInArrears”: “-2”
}-
4: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘11’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘11’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “11”
“AverageDaysInArrears”: “-1”
}-
5: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘10’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘10’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “10”
“AverageDaysInArrears”: “-6”
}-
6: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘09’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘09’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “09”
“AverageDaysInArrears”: “-2”
}-
7: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘08’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘08’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “08”
“AverageDaysInArrears”: “-4”
}-
8: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘07’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘07’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “07”
“AverageDaysInArrears”: “1”
}-
9: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘06’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘06’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “06”
“AverageDaysInArrears”: “-2”
}-
10: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘05’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘05’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “05”
“AverageDaysInArrears”: “-3”
}-
11: {
“metadata”: {
“id”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘04’)”
“uri”: “http://127.0.0.1:8080/PaymentBehaviourCollection(CustomerNo=‘0000801120’,CompanyCode=‘1000’,Year=‘2014’,Month=‘04’)”
“type”: “fincustfactsheet.PaymentBehaviour”
}-
“CustomerNo”: “0000801120”
“CompanyCode”: “1000”
“Year”: “2014”
“Month”: “04”
“AverageDaysInArrears”: “-3”
}-

}-
}[/code]

I want to get the Months, and AverageDaysInArrears.

For the AverageDaysInArreas I tried a lot of ways. I think it have to be something like:

[In String_Data is the JSON]

    dim root as new JSONItem( String_Data)
    dim rootItem as JSONItem = root.Value( "d" )
    
    dim readname as string = rootItem.Value( "AverageDaysInArrears" )
    MsgBox( readname )

The rootItem.Value( “AverageDaysInArrears” ) crashed in the Debugger. So I try something like:

    dim root as new JSONItem( String_Data)
    dim rootItem as JSONItem = root.Value( "d" )
    
    dim readname as string = rootItem.child(0).Value( "AverageDaysInArrears" )
    MsgBox( readname )

But it crashed again.

Eli_Ott · March 9, 2016, 11:24am

The above string is not a valid JSONItem.

Joost_Rongen · March 9, 2016, 11:25am

Think this one should be in another channel, not Customer Service.

Eli_Ott · March 9, 2016, 11:39am

Don’t know if this is the JSON you really have:

{ "d": { "results": [ 12 ], "0": { "__metadata": { "id": "http://127.0.0.1:8080/Payment...", "uri": "http://127.0.0.1:8080/Payment...", "type": "fincustfactsheet.PaymentBehaviour" }, "CustomerNo": "0000801120", "CompanyCode": "1000", "Year": "2015", "Month": "04", "AverageDaysInArrears": "-2" }, "1": { "__metadata": { "id": "http://127.0.0.1:8080/Payment...", "uri": "http://127.0.0.1:8080/Payment...", "type": "fincustfactsheet.PaymentBehaviour" }, "CustomerNo": "0000801120", "CompanyCode": "1000", "Year": "2015", "Month": "02", "AverageDaysInArrears": "-3" }, ... } }
This would be the code:

[code] Dim json As New JSONItem(s)

Dim d_Item As JSONItem = json.Child(“d”)
Dim results_Array As JSONItem = d_Item.Child(“results”)
Dim result As Integer = results_Array.Value(0).IntegerValue

For i As Integer = 0 To result - 1
Dim arr As JSONItem = d_Item.Child(Str(i))
Dim averageDaysInArrears As String = arr.Value(“AverageDaysInArrears”).StringValue
…
Next[/code]

Thomas_Mueller · March 9, 2016, 1:22pm

Thanks Eli Ott,

but that code crash in the line:

Dim result As Integer = results_Array.Value(0).IntegerValue

because of a type mismatch problem.

Eli_Ott · March 9, 2016, 1:30pm

Then your JSON is different than what I tried to reconstruct from the invalid JSON string in your original post.

Thomas_Mueller · March 9, 2016, 1:40pm

Hmm, the JSON must be valid, because it is from a SAP Fiori App - and I copy it 1:1, I only changed the IP Address from the correct URL to 127.0.0.1.

Kem_Tekinay · March 9, 2016, 1:48pm

Something must have happened in the copy and paste then, because Eli is right, that text, as posted, is not valid JSON. For example, something like this is not valid:

}-
"CustomerNo": "0000801120"
"CompanyCode": "1000"
"Year": "2015"
"Month": "04"
"AverageDaysInArrears": "-2"
}-

Thomas_Mueller · March 9, 2016, 2:16pm

Now I have the RAW Data without a pretty printer:

Is this valid JSON Code?


{"d":{"results":[{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2015',Month='04')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2015',Month='04')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2015","Month":"04","AverageDaysInArrears":"-2"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2015',Month='02')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2015',Month='02')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2015","Month":"02","AverageDaysInArrears":"-3"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2015',Month='01')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2015',Month='01')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2015","Month":"01","AverageDaysInArrears":"-5"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='12')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='12')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"12","AverageDaysInArrears":"-2"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='11')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='11')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"11","AverageDaysInArrears":"-1"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='10')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='10')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"10","AverageDaysInArrears":"-6"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='09')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='09')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"09","AverageDaysInArrears":"-2"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='08')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='08')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"08","AverageDaysInArrears":"-4"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='07')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='07')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"07","AverageDaysInArrears":"1"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='06')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='06')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"06","AverageDaysInArrears":"-2"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='05')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='05')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"05","AverageDaysInArrears":"-3"},{"__metadata":{"id":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='04')","uri":"http://127.0.0.1/fincustfactsheet/PaymentBehaviourCollection(CustomerNo='0000801120',CompanyCode='1000',Year='2014',Month='04')","type":"fincustfactsheet.PaymentBehaviour"},"CustomerNo":"0000801120","CompanyCode":"1000","Year":"2014","Month":"04","AverageDaysInArrears":"-3"}]}}

Wayne_Golding · March 9, 2016, 6:03pm

Xojo.Data http://developer.xojo.com/xojo-data$ParseJSON parses JSON much better than the classic version.

Eugene_Dakin · March 10, 2016, 1:46am

Yes, the second posting of JSON is valid. Here is the website that was used to check JSON: JSON Formatter

Thomas_Mueller · March 10, 2016, 8:18am

Thanks Eugene Dakin, that was that what I searched for! Now it works! THANK YOU!