Help with JSON parse

[ironfelix717]

Applauding computer science --- we've actually counterproductively made what is supposed to be a universal data structure into a PITA to use.

I am trying to use the bloated JSON parser to grab a value from a JSON string.

Frankly, I don't understand dictionaries that well. And now were gonna nest them - sweet.

Here is the JSON (unformatted crap):

{"request_info":{"success":true},"account_info":{"api_key":"NoneUrBiz","name":"Johnson","email":"fruit@gmail.com","plan":"Starter","credits_used":3,"credits_remaining":9997,"credits_reset_at":"2022-02-20T22:19:27.000Z","credits_limit":10000,"zipcodes_used":0,"zipcodes_limit":1,"zipcodes_available":1,"account_balance_usd":0,"account_balance_message":"Your Account Balance is used to pay any Overage charges (to avoid multiple small transactions being made to your payment card). You can top up your Account Balance on the Account page of the Dashboard. Your Account Balance is not used for your monthly Plan payment.","destinations_used":0,"destinations_limit":50,"destinations_available":50,"overage_allowed":true,"overage_enabled":false,"overage_limit":20000,"overage_used":0,"collections_used":0,"collections_limit":10000,"collections_available":10000,"usage_history":[{"month":"January","year":2022,"month_number":1,"is_current_month":true,"credits_total_for_month":3,"credits_total_per
_day":{"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":3,"21":0,"22":0,"23":0,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0}},{"month":"December","year":2021,"month_number":12,"is_current_month":false,"credits_total_for_month":0,"credits_total_per_day":{"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":0,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0}},{"month":"November","year":2021,"month_number":11,"is_current_month":false,"credits_total_for_month":0,"credits_total_per_day":{"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":0,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0}},{"month":"October","year":2021,"month_number":10,"is_current_month":false,"credits_total_for_month":0,"credits_total_per_day":{"1":0,"2":
0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":0,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0}}],"status":[{"component":"Search Requests","description":"Status of CnodeAPIDataSystems 'type=search' requests","group":"Parsing","status":"operational"},{"component":"Product Requests","description":"Status of CnodeAPIDataSystems 'type=product' requests","group":"Parsing","status":"operational"},{"component":"Offers Requests","description":"Status of CnodeAPIDataSystems 'type=offers' requests","group":"Parsing","status":"operational"},{"component":"Stock Estimation Requests","description":"Status of CnodeAPIDataSystems 'type=stock_estimation' requests","group":"Parsing","status":"operational"},{"component":"Reviews Requests","description":"Status of CnodeAPIDataSystems 'type=reviews' requests","group":"Parsing","status":"operational"},{"component":"Bestmanagers Requests","description":"Status of CnodeAPIDataSystems 'type=bestmanagers' requests","group":"Parsing","sta
tus":"operational"},{"component":"Questions Requests","description":"Status of PULLTERM 'type=questions' requests","group":"Parsing","status":"operational"},{"component":"Category Requests","description":"Status of CnodeAPIDataSystems 'type=category' requests","group":"Parsing","status":"operational"},{"component":"Also Bought Requests","description":"Status of CnodeAPIDataSystems 'type=also_bought' requests","group":"Parsing","status":"operational"},{"component":"manager Profile Requests","description":"Status of CnodeAPIDataSystems 'type=manager_profile' requests","group":"Parsing","status":"operational"},{"component":"Deals Requests","description":"Status of CnodeAPIDataSystems 'type=deals' requests","group":"Parsing","status":"operational"},{"component":"Autocomplete Requests","description":"Status of CnodeAPIDataSystems 'type=autocomplete' requests","group":"Parsing","status":"operational"},{"component":"Author Page Requests","description":"Status of CnodeAPIDataSystems 'type=author_page' requests","group":"Parsing","status":"operational"}]}}

And here is my code using the JSON parser: (Tim Halls Code: https://github.com/VBA-tools/VBA-JSON)

Dim ret As Dictionary
Dim val As Variant

Set ret = ParseJson(resp)

For Each val In ret("account_info")
    Debug.Print val
Next

This gives...

api_key
name
email
plan
credits_used
credits_remaining
credits_reset_at
credits_limit
zipcodes_used
zipcodes_limit
zipcodes_available
account_balance_usd
account_balance_message
destinations_used
destinations_limit
destinations_available
overage_allowed
overage_enabled
overage_limit
overage_used
collections_used
collections_limit
collections_available
usage_history
status

I need to be able to grab the values of these properties! How do I do this.



Thanks

 

[theDBguy]

Hi. Not in front of a computer now, but have you tried?

Debug.Print val(0)

If that doesn't work, you could try using a different variable name, since Val() is a built in VBA function.

Hope that helps...

 

[ironfelix717]

Hi. Not in front of a computer now, but have you tried?

Debug.Print val(0)

If that doesn't work, you could try using a different variable name, since Val() is a built in VBA function.

Hope that helps...

@theDBguy

Yep tried that... As for 'Val' i've been using it for a long time and never had an issue. Unsure if 'val' is an actual keyword, if it is, i still probably won't change

Maybe its the JSON parser. Have no idea how i'm supposed to grab a property's value.
Hey but the entire module of code should be able to parse out a few simple lines of JSON right? .... L O L

 

[arnelgp]

i tested it but the parser says it "has no row"?

 

[ironfelix717]

i tested it but the parser says it "has no row"?

Parser must be trash

 

[arnelgp]

create a New Text Document.txt to your Desktop and paste your json string and close.
and run StoretHTTPToTextFile() (in ModJson).
you will see the Output in Result sheet.

 

[Galaxiom]

Yet another of hundreds of reasons developers would be advised to progress into SQL Server. It has native JSON capabilities.

 

[isladogs]

Tim Hall's code is a good starting point for handling JSON files but it can be defeated by even simple files that are badly structured.
I have my own app for JSON: https://www.isladogs.co.uk/json-analyse-transform/

I read in your file with no issues. According to this, it apparently has 42 fields and one subarray.

See PDF report attached

EDIT:
Your file includes 30 fields with numbers as field names - 2 through to 31.
That indicates a poorly structured source file and the number fields will cause issues for Access

 

[ironfelix717]

@isladogs i saw your app and didn't download it. I need functions not apps. However, I will analyze it and see if I can use any of the code.

As for Tim Halls code... That many lines and there's no check on the structure of the file? Nice.

As for the JSON file, I have no control over its format. Have to work with what I'm given.

Thanks

 

[theDBguy]

As for Tim Halls code... That many lines and there's no check on the structure of the file? Nice.

I use it fine. Try changing your code from this:

    Debug.Print val

to this:

    Debug.Print ret("account_info")(val)

Hope that helps...

 

[isladogs]

Two other methods of handling JSON files are to use Get & Transform AKA PowerQuery in Excel or Powershell.
No code needed but there is a steep learning curve in each case.

 

[ironfelix717]

OK still having troubles with this. For the sake of everyones sanity - I can grab the value I need from this particular string no problem. But the project I just took on (unprofessional) requires a lot of JSON interaction and I need to be able to easily access/store data with some level of reliability - with (hopefully) not having to learn the entire JSON spec. I just need to grab some data, but do it often.

@isladogs I haven't got a chance to review your file yet.

@arnelgp Your parser at least runs on my file. And its pretty simple and understandable. Worst case scenario I can build around it to suit my needs to do 'rough queries' on the JSON. However your parser did not parse correctly in my case. Its almost as if it stopped a "level" short in the file.

Here is an example of output on the file in question:

js.LoadTheJSONString (resp)  'resp = string var of HTTPGET call to my API

If js.err <> 1 Then
    'fail
    MsgBox "Failed"
Else
    For i = 1 To js.NumElements
        Debug.Print js.Key(i) & "==" & js.Value(i)
    Next i
End If


OUTPUT:
>account_info>usage_history>month==January  'valid
>account_info>usage_history>year==2022  'valid
>account_info>usage_history>month_number==1  'valid
>account_info>usage_history>is_current_month==true  'valid
>account_info>usage_history>credits_total_for_month==4  'incorrect
>account_info>usage_history>credits_total_per_day>1==0   'incorrect...
>account_info>usage_history>credits_total_per_day>2==0
>account_info>usage_history>credits_total_per_day>3==0
>account_info>usage_history>credits_total_per_day>4==0
>account_info>usage_history>credits_total_per_day>5==0
>account_info>usage_history>credits_total_per_day>6==0
>account_info>usage_history>credits_total_per_day>7==0

@arnelgp Any ideas?

Thanks

 

[arnelgp]

on the Excel sheet that i posted:

 

[ironfelix717]

Update:

The API i am interfacing with has a CSV data return option. So, i'm no longer even attempting to waste time with JSON.

Your parser does work pretty decent though.

Thanks for the help - closing thread.