complex Json with data

[bman9]

I am trying to plug the following code into cells using VBA. I can get the data from the "data" section but I also need to include data from the "included" Section, more specifically the "product_name".

VBA Code:

Dim JSON As Object
Dim i As Integer
Set JSON = JsonConverter.ParseJson(xmlhttp.responseText)

i = 2

Dim data As Variant

Dim object As Collection

ReDim data(JSON("data").Count, 5)

For Each value In JSON("data")

Sheets(1).Cells(i, 1).value = value("id")

Sheets(1).Cells(i, 2).value = value("attributes")("customer_first_name")

Sheets(1).Cells(i, 3).value = value("attributes")("customer_last_name")

Sheets(1).Cells(i, 4).value = value("attributes")("customer_email")

Sheets(1).Cells(i, 5).value = value("items[3].product_name")

Sheets(1).Cells(i, 6).value = value("attributes")("shipping_address_1")

Sheets(1).Cells(i, 7).value = value("attributes")("shipping_address_2")

Sheets(1).Cells(i, 8).value = value("attributes")("shipping_city")

Sheets(1).Cells(i, 9).value = value("attributes")("shipping_state")

Sheets(1).Cells(i, 10).value = value("attributes")("shipping_zip")

Sheets(1).Cells(i, 11).value = value("attributes")("shipping_latitude")

Sheets(1).Cells(i, 12).value = value("attributes")("shipping_longitude")

Sheets(1).Cells(i, 13).value = value("attributes")("created_at")

'Sheets(1).Cells(i, 14).value = value("included")(3)("attributes")("price")

Sheets(1).Cells(i, 15).value = value("attributes")("item_count")

Sheets(1).Cells(i, 16).value = value("attributes")("total")

i = i + 1

Next value

For Each value In JSON("included")
ReDim included(JSON("included").Count, 3)
Sheets(1).Cells(i, 5).value = value("attributes")("product_name")
Sheets(1).Cells(i, 14).value = value("attributes")("price")

Next value

MsgBox ("complete")

End Sub

JSON code

{
"data": [
{
"id": "LMFN-543196",
"type": "orders",
"attributes": {
"item_count": 1,
"item_total": "100.1",
"discount_total": "10.0",
"shipping_total": "10.0",
"tax_total": "0.0",
"total": "100.1",
"customer_first_name": "Joe",
"customer_last_name": "Somebody",
"customer_email": "joe@somebody.com",
"customer_opted_in_to_marketing": false,
"customer_note": "Created by Joe",
"shipping_address_1": "123 some street",
"shipping_address_2": "#123",
"shipping_city": "Somewhere",
"shipping_state": "UT",
"shipping_zip": "64801",
"shipping_latitude": null,
"shipping_longitude": null,
"shipping_status": "unshipped",
"payment_status": "completed",
"created_at": "2014-12-25T00:00:00.000Z",
"updated_at": "2014-12-25T00:00:00.000Z",
"completed_at": "2014-12-25T00:00:00.000Z"
},
"links": {
"self": "https://api.bigcartel.com/v1/accounts/1/orders/LMFN-543196"
},
"relationships": {
"currency": {
"data": {
"type": "currencies",
"id": "USD"
}
},
"shipping_country": {
"data": {
"type": "countries",
"id": "US"
}
},
"events": {
"data": [
{
"type": "order_events",
"id": "1"
}
]
},
"items": {
"data": [
{
"type": "order_line_items",
"id": "2"
}
]
},
"transactions": {
"data": [
{
"type": "order_transactions/payments",
"id": "3"
}
]
},
"adjustments": {
"data": [
{
"type": "order_adjustments/shipping",
"id": "4"
},
{
"type": "order_adjustments/tax",
"id": "5"
},
{
"type": "order_adjustments/discount",
"id": "6"
}
]
}
}
}
],
"meta": {
"count": 25
},
"included": [
{
"id": "USD",
"type": "currencies",
"attributes": {
"name": "U.S. Dollar",
"sign": "$",
"locale": "en-US"
}
},
{
"id": "US",
"type": "countries",
"attributes": {
"name": "United States"
}
},
{
"id": "1",
"type": "order_events",
"attributes": {
"created_at": "2014-12-25T00:00:00.000Z",
"message": "Payment completed"
}
},
{
"id": "2",
"type": "order_line_items",
"attributes": {
"product_name": "This product",
"product_option_name": "This option",
"quantity": 1,
"price": "100.0",
"total": "100.0",
"image_url": "https://images.bigcartel.com/some_resource/12345/-/example.jpg"
},
"relationships": {
"product": {
"data": {
"type": "product",
"id": "7"
}
},
"product_option": {
"data": {
"type": "product_option",
"id": "8"
}
}
}
},
{
"id": "3",
"type": "order_transactions/payments",
"attributes": {
"label": "Visa ending in 1234",
"amount": "100.0",
"processor": "stripe",
"processor_id": "ex_123456789",
"processor_url": "https://manage.stripe.com/test/payments/ex_123456789"
},
"data": {
"currency": {
"data": {
"type": "currencies",
"id": "USD"
}
}
}
},
{
"id": "4",
"type": "order_adjustments/shipping",
"attributes": {
"amount": "10.0",
"label": "Shipping charges"
}
},
{
"id": "5",
"type": "order_adjustments/tax",
"attributes": {
"amount": "0.1"
}
},
{
"id": "6",
"type": "order_adjustments/discount",
"attributes": {
"amount": "10.0",
"label": "[FREESHIPPING] Free shipping discount"
}
}
],
"links": {
"next": "https://api.bigcartel.com/v1/accounts/1/orders?page%5Blimit%5D=10&page%5Boffset%5D=10",
"last": "https://api.bigcartel.com/v1/accounts/1/orders?page%5Blimit%5D=10&page%5Boffset%5D=10"
}
}

[reptileqc]

I don't quite understand the reason for the ReDim in the original example (on the project page). Here is how I made it work (code sample from Access).

Dim JSON As Object
Set JSON = JsonConverter.ParseJson(xmlhttp.responseText)

    Dim dLevelOne As Dictionary
    Dim dLevelTwo As Dictionary ' Not used in this example
    
    ' Data
    For Each dLevelOne In JSON("data")
    
        If dLevelOne("type") = "orders" Then
            Debug.Print "Level 1:" & dLevelOne("id")
        End If
        
    Next dLevelOne
    
    ' Included
    For Each dLevelOne In JSON("included")
        If dLevelOne("id") = 2 Then
            Debug.Print dLevelOne("attributes")("product_name")
        Else
            Debug.Print dLevelOne("id")
        End If
    Next dLevelOne

Basically from what I can understand every [ ] has to be declared as a dictionary in order to go through each value. Then every { } can be accessed directly if it is inside the iteration. For multiple levels of { } simply use the syntax Dictionary("level one")("level two")("level three")

Hope it helps.

Alex

[reptileqc]

I also found this site to be a great help into parsing and viewing the data :

https://jsonformatter.curiousconcept.com/

Alex