Parse Json Nested Array in VBA (1 Viewer)

[Badeye]

Hi Guys,

I'm currently working on an API feed in Access but the one thing I am struggling with how to extra the values for that are in the nested arrays that come form the Json reponse.

The array are basically image urls, sometimes there maybe more than 1.

Can anyone help me cycle through the nested array so I can pull out these values?

Thanks in advance.

 

[jdraw]

Search this forum for JSON PARSER by user Ridders.

 

[isladogs]

Did I hear my name?

Have a look here for an early version of my JSON parser
https://www.access-programmers.co.uk/forums/showthread.php?t=295789

Or post the json file and I'll see if I can parse it for you

 

[Badeye]

Hi ridders,

I already have a json parser which I probably got from here which works really well. and I can pull the values from every element other than the arrays.

This is my result from the json parser.

[{"productId":"33e3e81d-2b78-475a-8886-9848116f5133","identifier":"TPP","name":"Test pre order product","platform":"Origin","quantity":0,"images":[{"image":"https://sandbox.codeswholesale.com/assets/images/no-image.jpg","format":"SMALL"},{"image":"https://sandbox.codeswholesale.com/assets/images/no-image.jpg","format":"MEDIUM"}],"regions":["ASIA"],"languages":["Multilanguage"],"prices":[{"value":0.10,"from":100,"to":0},{"value":1.10,"from":1,"to":9},{"value":0.40,"from":10,"to":99}],"links":[{"rel":"self","href":"https://sandbox.codeswholesale.com/v1/products/33e3e81d-2b78-475a-8886-9848116f5133"},{"rel":"buy","href":"https://sandbox.codeswholesale.com/v1/orders?productId=33e3e81d-2b78-475a-8886-9848116f5133"},{"rel":"description","href":"https://sandbox.codeswholesale.com/v1/products/33e3e81d-2b78-475a-8886-9848116f5133/description"}],"releaseDate":"2055-04-30T22:00:00.000Z"},{"productId":"04aeaf1e-f7b5-4ba9-ba19-91003a04db0a","identifier":"TPWHP","name":"Test product with high price","platform":"None","qua
ntity":0,"images":[{"image":"https://sandbox.codeswholesale.com/assets/images/no-image.jpg","format":"SMALL"},{"image":"https://sandbox.codeswholesale.com/assets/images/no-image.jpg","format":"MEDIUM"}],"regions":["WORLDWIDE"],"languages":["Multilanguage"],"prices":[{"value":1038999996.88,"from":100,"to":0},{"value":1038999997.18,"from":10,"to":99},{"value":1038999997.88,"from":1,"to":9}],"links":[{"rel":"self","href":"https://sandbox.codeswholesale.com/v1/products/04aeaf1e-f7b5-4ba9-ba19-91003a04db0a"},{"rel":"buy","href":"https://sandbox.codeswholesale.com/v1/orders?productId=04aeaf1e-f7b5-4ba9-ba19-91003a04db0a"},{"rel":"description","href":"https://sandbox.codeswholesale.com/v1/products/04aeaf1e-f7b5-4ba9-ba19-91003a04db0a/description"}],"releaseDate":null},{"productId":"6313677f-5219-47e4-a067-7401f55c5a3a","identifier":"TWICO","name":"Test with image codes only","platform":"Steam","quantity":186,"images":[{"image":"https://sandbox.codeswholesale.com/assets/images/no-image.jpg","format":"SMALL"},{"image
":"https://sandbox.codeswholesale.com/assets/images/no-image.jpg","format":"MEDIUM"}],"regions":["EU"],"languages":["fr"],"prices":[{"value":0.10,"from":100,"to":0},{"value":0.40,"from":10,"to":99},{"value":1.10,"from":1,"to":9}],"links":[{"rel":"self","href":"https://sandbox.codeswholesale.com/v1/products/6313677f-5219-47e4-a067-7401f55c5a3a"},{"rel":"buy","href":"https://sandbox.codeswholesale.com/v1/orders?productId=6313677f-5219-47e4-a067-7401f55c5a3a"},{"rel":"description","href":"https://sandbox.codeswholesale.com/v1/products/6313677f-5219-47e4-a067-7401f55c5a3a/description"}],"releaseDate":null},{"productId":"ffe2274d-5469-4b0f-b57b-f8d21b09c24c","identifier":"TWTCO","name":"Test with text codes only","platform":"Uplay","quantity":0,"images":[{"image":"https://sandbox.codeswholesale.com/assets/images/no-image.jpg","format":"SMALL"},{"image":"https://sandbox.codeswholesale.com/assets/images/no-image.jpg","format":"MEDIUM"}],"regions":["PL"],"languages":["pl"],"prices":[{"value":1.11,"from":1,"to":9},{"
value":0.11,"from":100,"to":0},{"value":0.41,"from":10,"to":99}],"links":[{"rel":"self","href":"https://sandbox.codeswholesale.com/v1/products/ffe2274d-5469-4b0f-b57b-f8d21b09c24c"},{"rel":"buy","href":"https://sandbox.codeswholesale.com/v1/orders?productId=ffe2274d-5469-4b0f-b57b-f8d21b09c24c"},{"rel":"description","href":"https://sandbox.codeswholesale.com/v1/products/ffe2274d-5469-4b0f-b57b-f8d21b09c24c/description"}],"releaseDate":null}]

I am using the following to pull out the values

For Each element In p
    
    prodid = element("productId")
    identifier = element("identifier")
    prodname = element("name")
    platform = element("platform")
    qty = element("quantity")

   Next element

Please see the attached.

You'll see the element images is an array but I need some code so I can extract those. I will be running this API feed a few time per day.

If you can help, that would be amazing.

Cheers,

 

[isladogs]

1. Nothing attached

2. Please check whether you are using my parser / analyser.
If you are it will make it much easier to explain.
And if you're not, why not ....

3. The latest version of mine can handle nested 'subarrays'

 

[Badeye]

I forgot to remove the text saying there was something attached, I embedded the json instead.
I am not using your parser, to be honest I probably didn't know about it until now but the one I am using is working fine.

I just need to know how to get these array values out. It's a right pain.

 

[isladogs]

Ok, what are you using? There aren't many around.
And if it can't handle subarrays, then its not going to be adequate for most JSON files

I'll have a look at this later this morning UK time but in the meantime I suggest you at least skim read the PDF user guide for v4.05 of mine.

 

[isladogs]

My JSON parser shows the following structure:

So it needs 3 tables to ensure normalisation and the transform function I created should be capable of doing just that.
There are more fields in the regions subarray that it hasn't picked up due to errors in the file structure (see below)

Here's the main table of the 3 you need. It has 4 records

However I got an error when my function was populating the 2 subtables
Running your json file through an online json validator shows there are file errors - you should be able to see why from this screenshot
The issue affects both the lines shown and subsequent lines.

I can fix the errors manually but you say its not a one-off import
As you are going to be doing this several times per day, you need to get the file structure correct if you have any influence over it.
Otherwise, my parser can treat files as special cases to fix parsing issues.
However, doing so takes time & I'd have to charge for doing that.

Suggest you either PM me or better still email me using the link below if you wish me to spend more time on this

A transform function can fairly easily be created just for this file (and someone else may well offer you just that)
However, in my view it would be better to have a long term solution

 

[isladogs]

I've spent several minutes fixing the errors in your file

There are actually 5 subarrays -
- images / regions / languages / prices / links

I ran the analyser again & its detected all of these subarrays giving a total of 15 fields.
It did also pick up one spurious field (Images) but that's easy to exclude

And here is the output as 6 tables. - I've used subdatasheets below so you can see all the 6 tables

I won't do any more to this unless I hear back from you

 

[arnelgp]

this is what i get from my own analyser:


productId
identifier
name
platform
quantity
images/image
images/format
regions/1
languages/1
prices/value
prices/from
prices/to
links/rel
links/href
releaseDate
qua

 

[isladogs]

this is what i get from my own analyser:


productId
identifier
name
platform
quantity
images/image
images/format
regions/1
languages/1
prices/value
prices/from
prices/to
links/rel
links/href
releaseDate
qua

Correct except for the last one : 'qua' doesn't exist
The 1 just indicates the number of values in the 2 values subarrays

Out of interest, what are you using to analyse the file?

 

[Badeye]

Hi Guys,

Don't worry too much about the single elements, all I need to know is a way of getting the image value from images/image and format from images/format. All the other values I can extract fine, its just these array values i'm struggling with.

Cheers,

 

[arnelgp]

yes, uts not included, its quantity.

 

[isladogs]

I'll give you most of the solution...

Transform function attached but it can't be used as written without some additional items:
a) I've not included some functions that it references
b) You will need to define various variables used in the function that I've defined at module level
c) You will need to create the 6 tables yourself