VBA-JSON Create nested objects

Question

I have seen a lot of examples showing how to parse json strings with VBA-JSON, however I would like to know how to create a JSON object from scratch using this library.

I have started with:

Set Json = JsonConverter.ParseJson("{}")
Json("key") = "value"

And this works as expected.

However, if I want to create a nested object, for example:

Json("new_key")(1)("value") = 1
Json("new_key")(2)("foo") = "bar"

Does not output the expected: {"new_key":[{"value": 1}, {"foo": "bar"}]}

Is this possible to achieve with this library? Or is there another way to do it?

Thanks

What about myNest = "{""new_key"":{""value"":1,""foo"":""bar""}}" — BruceWayne
– BruceWayne, Commented Feb 28, 2018 at 15:43
Does not seem to do it...it will map the key to a string, and gives an error when trying to access individual values as Json("new_key")("value") — drec4s
– drec4s, Commented Feb 28, 2018 at 15:47

xuanhai266 · Accepted Answer · 2018-02-28 16:12:36Z

12

You can use Dictionary and Collection in VBA. After that convert them to Json. This is an example:

Sub test()
    Dim c As Collection
    Dim d As Dictionary
    Dim e As Dictionary
    Dim f As Dictionary
    Dim json As String

    Set c = New Collection
    Set d = New Dictionary
    Set e = New Dictionary
    Set f = New Dictionary

    d.Add "value", 1
    e.Add "foo", "bar"
    c.Add d
    c.Add e
    f.Add "new_key", c

    json = JsonConverter.ConvertToJson(ByVal f)

    Debug.Print json
End Sub

And this is output:

{"new_key":[{"value":1},{"foo":"bar"}]}

answered Feb 28, 2018 at 16:12

xuanhai266

4334 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2018-02-28 16:55:54Z

5

Something like this:

Set Json = JsonConverter.ParseJson("{}")
'or
'Set Json = CreateObject("scripting.dictionary")

Json.Add "new_key", CreateObject("scripting.dictionary")
Json("new_key")("value") = 1
Json("new_key")("foo") = "bar"

edited Feb 28, 2018 at 16:55

answered Feb 28, 2018 at 16:10

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

2 Comments

Tim Williams Over a year ago

I just noticed I maybe mis-read your required output: @xuanhai266's solution is a better match.

drec4s Over a year ago

Yes, but in fact all I really wanted was to understand the creation of a new nested key, and this line Json.Add "new_key", CreateObject("scripting.dictionary") did the trick.

Collectives™ on Stack Overflow

VBA-JSON Create nested objects

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related