VB6 - JsonBag, Another JSON Parser/Generator

With SOAP basically a dead duck and XML itself fading in importance, JSON is becoming more and more important as a serialization format today.

I've seen a number of VB6 JSON implementations around, including a couple posted right here in the CodeBank. Sadly none of them are very good, with little quirks and latent bugs (like improperly handling numeric data). Most of these ignore and flout the JSON standards at JSON too!

In any case I have my own implementation designed to conform as closely as possible to the standard, and now at version 1.6 it seems mature enough to share. I rewrote my notes into six pages of documentation which should make it a bit easier for others to use.


Bug reports are welcome. Though it seems to be working fine it is hard to prove code to be correct and there are probably edge cases I've never encountered.

Performance seems more than adequate for its purpose, which is mainly access to cloud computing services. Local config files are probably best still handled as plain old INI format files, though one could use JSON for that as well I suppose.


There is just one Class involved: JsonBag.cls, and as the documentation suggests it should be easy enough to import into a VBA host (Excel, etc.) as long as you tweak the API calls for 64-bit hosts when required.


The attachment includes this Class along with the documentation in RTF format, packaged up as a testbed Project JsonBagTest.vbp:

Attachment 106027

As you can see JsonBag supports a Whitespace property to format JSON for readability. By default compact JSON is generated.


Accessing the "document model" and creating JSON documents in code is easy enough. This is illustrated by a fragment from the test Project:

Code:

---

Private Sub cmdGenSerialize_Click()
    With JB
        .Clear
        .IsArray = False 'Actually the default after Clear.

        ![First] = 1
        ![Second] = Null
        With .AddNewArray("Third")
            .Item = "These"
            .Item = "Add"
            .Item = "One"
            .Item = "After"
            .Item = "The"
            .Item = "Next"
            .Item(1) = "*These*" 'Should overwrite 1st Item, without moving it.

            'Add a JSON "object" to this "array" (thus no name supplied):
            With .AddNewObject()
                .Item("A") = True
                !B = False
                !C = 3.14E+16
            End With
        End With
        With .AddNewObject("Fourth")
            .Item("Force Case") = 1 'Use quoted String form to force case of names.
            .Item("force Case") = 2
            .Item("force case") = 3

            'This syntax can be risky with case-sensitive JSON since the text is
            'treated like any other VB identifier, i.e. if such a symbol ("Force"
            'or "Case" here) is already defined in the language (VB) or in your
            'code the casing of that symbol will be enforced by the IDE:

            ![Force Case] = 666 'Should overwrite matching-case named item, which
                                'also moves it to the end.
            'Safer:
            .Item("Force Case") = 666
        End With
        'Can also use implied (default) property:
        JB("Fifth") = Null

        txtSerialized.Text = .JSON
    End With
End Sub

---

Newst version here.

Here is another demo that may be helpful in understanding how to use JsonBag.

It loads and parses a big complicated JSON document and displays it. Then it traverses and dumps the parsed JSON in the JsonBag object hierarchy. Finally it displays an item from within the hierarchy using two possible VB syntaxes.

Attachment 106173

great, works like a charm. here is an online one http://newexception.com/tools/json-validator

Quote:

---

Originally Posted by dilettante

Here is another demo that may be helpful in understanding how to use JsonBag.

It loads and parses a big complicated JSON document and displays it. Then it traverses and dumps the parsed JSON in the JsonBag object hierarchy. Finally it displays an item from within the hierarchy using two possible VB syntaxes.

Attachment 106173

---

This was really fun to watch work until it blew up with an "invalid procedure call or argument - 5" error on this line;
If IsObject(Values.Item(PrefixedKey)) Then

PrefixedKey value was; 6D0D431Eweb-app

Quote:

---

Originally Posted by y2kdeuce

This was really fun to watch work until it blew up with an "invalid procedure call or argument - 5" error on this line;
If IsObject(Values.Item(PrefixedKey)) Then

PrefixedKey value was; 6D0D431Eweb-app

---

It is worth noting that any test done within the IDE should have Break on Unhandled Errors selected, or false stops are inevitable. This of course doesn't matter in compiled programs.

Is it possible to parse json string without the recursive example? for example, how do i parse this string in a for/foreach loop

Code:

---

{
  "status": "error",
  "request": [
    "sdgsdg",
    "sgdgsdg",
  ],
  "update": [
    {
      "id": "1",
      "val": "rqrq",
    },
    {
      "id": "3",
      "val": "rqwerq",
    }
  ]
}

---

I don't see why not, but that would be an entirely different solution.

in php for the json string above, i could do

Code:

---

$decoded=json_decode($json_string);
$status=$decoded->status
$request=$decoded->request
foreach($request as $value){
echo $value
}

---

can i do the same way with jsonbag?

Ahh.

I misunderstood your question. You aren't asking about parsing at all.

Scroll down to the blue lines here:

Code:

---

Option Explicit

Private Sub Echo(ByVal Value As Variant)
    With Text1
        .SelStart = &H7FFF
        If IsNull(Value) Then
            .SelText = "#NULL"
        ElseIf IsEmpty(Value) Then
            .SelText = "#EMPTY"
        ElseIf VarType(Value) = vbObject Then
            If TypeOf Value Is JsonBag Then
                If Value.IsArray Then
                    .SelText = "#JSON array"
                Else
                    .SelText = "#JSON list"
                End If
            Else
                .SelText = "#instance of " & TypeName(Value)
            End If
        Else
            .SelText = CStr(Value)
        End If
        .SelText = vbNewLine
    End With
End Sub

Private Sub Form_Load()
    Dim F As Integer
    Dim JSON As String
    Dim JB As JsonBag
    Dim I As Long
    Dim Value As Variant

    F = FreeFile(0)
    Open "json.txt" For Input As #F
    JSON = Input$(LOF(F), #F)
    Close #F

    Set JB = New JsonBag
    JB.JSON = JSON
    Echo JB.Item("status")
    'Must enumerate JSON arrays by index, not with For...Each:
    With JB.Item("request")
        For I = 1 To .Count
            Echo .Item(I)
        Next
    End With
End Sub

---

You can use For...Each with lists however.

thank you, you are the man

I tried to check if an item exists in the above json string

Code:

---

JB.Exists("status")

---

but it return false

You used collections in the jsonbag, i suggest dictionary as they are 10x faster than collections

Quote:

---

Originally Posted by coolcurrent4u

thank you, you are the man

I tried to check if an item exists in the above json string

Code:

---

JB.Exists("status")

---

but it return false

---

Thanks, you're right. There was a bug in one line. New version attached here.

Quote:

---

Originally Posted by coolcurrent4u

You used collections in the jsonbag, i suggest dictionary as they are 10x faster than collections

---

Feel free to do so if you wish.

I considered Scripting.Dictionary and discarded the idea. They can actually be much slower than Collections for enumeration, and you have a large library loaded that you normally don't need. Normally you don't do much local batch processing of JSON documents, so performance is far more limited by network speeds than Collections.

About the only place they were faster for this purpose was doing "exists" checks so if you are doing a huge amount of that Dictionary might be worth considering. Notice that I never stumbled over the "Exists bug" in JsonBag 1.6 myself? I hardly ever use it so when I created the bug I never even knew it.

If you have to validate every chunk of JSON ever sent to your code... you're going to have a ton of validation logic in there! You'll be far better off just using error trapping.

With collections existence check performance suffers greatly when key is not present because the built-in `Err` object has to be populated with error details. I use a typelib declared `IVbCollection` interface that returns `Long` instead of `HRESULT` on `Item` property (actually method) that is 10x times faster on [non-]existence check. A performace optimized `SearchCollection` function with this typelib looks like this:

Code:

---

Public Function SearchCollection(ByVal pCol As Object, Index As Variant, Optional RetVal As Variant) As Boolean
    Const DISPID_VALUE  As Long = 0
    Dim pVbCol          As IVbCollection
   
    If pCol Is Nothing Then
        '--- do nothing
    ElseIf TypeOf pCol Is IVbCollection Then
        Set pVbCol = pCol
        SearchCollection = pVbCol.Item(Index, RetVal) = 0
    Else
        SearchCollection = DispInvoke(pCol, DISPID_VALUE, ucsIclPropGet, Result:=RetVal, Args:=Index)
        If Not SearchCollection Then
            '--- some weird collections have default (Item) method
            SearchCollection = DispInvoke(pCol, DISPID_VALUE, ucsIclMethod, Result:=RetVal, Args:=Index)
        End If
    End If
End Function

---

My tests with `Scripting.Dictionary` vs `VB.Collection` for JSON containers resulted in 10x more memory usage with `Dictionaries`. Nevertheless I prefer using `Dictionaries` for JSON containers as I can use `CompareMode` property to distinguish object vs array containers.

cheers,
</wqw>

Quote:

---

Originally Posted by dilettante

Thanks, you're right. There was a bug in one line. New version attached here.

---

In your v1.7, testing code got error:

Debug.Print JB.Item("web-app").Item("servlet")(1).Item("init-param").Item("templateProcessorClass")

Seems to be working fine here.

What does "got error" mean? What error number? Error description?

Quote:

---

Originally Posted by dilettante

Seems to be working fine here.

What does "got error" mean? What error number? Error description?

---

Runtime error '438'
Object doesn't support this property and method.

I paste your v1.7 class to your demo2.

I did that here too, but it runs with no problem. I even copied your Debug line into the program and it still works.

Can you track down which line of code fails in the class?

Quote:

---

Originally Posted by dilettante

I did that here too, but it runs with no problem. I even copied your Debug line into the program and it still works.

Can you track down which line of code fails in the class?

---

Code:

---

Private Sub Form_Load()
    Dim F As Integer
    Dim JsonData As String
    Dim JB As JsonBag
   
    GapHorizontal = lblJson.Left
    GapVertical = lblJson.Top
   
    F = FreeFile(0)
    Open "JsonSample.txt" For Input As #F
    JsonData = Input$(LOF(F), #F)
    Close #F
    txtJson.Text = JsonData

    Set JB = New JsonBag
    JB.JSON = JsonData
    Show
    JbDump "*anonymous outer JsonBag*", JB
    HomeDump
   
    Debug.Print JB.Item("web-app").Item("servlet")(1).Item("init-param").Item("templateProcessorClass")

End Sub

---

I debug into the loop, seems to fail at .Item("servlet")(1).

If you copy and pasted the code, perhaps the procedure attributes did not come across? Therefore, you would not have a default attribute of the JsonBag class (Item), which would cause the error.

The following code should fix the problem, proving the above assertion:

Debug.Print JB.Item("web-app").Item("servlet").Item(1).Item("init-param").Item("templateProcessorClass")

Instead of copying the code, you should replace the code file directly in its entirety to get all of the procedure attributes that Dilettante has defined.

Quote:

---

Originally Posted by jpbro

If you copy and pasted the code, perhaps the procedure attributes did not come across? Therefore, you would not have a default attribute of the JsonBag class (Item), which would cause the error.

The following code should fix the problem, proving the above assertion:

Debug.Print JB.Item("web-app").Item("servlet").Item(1).Item("init-param").Item("templateProcessorClass")

Instead of copying the code, you should replace the code file directly in its entirety to get all of the procedure attributes that Dilettante has defined.

---

You are right. The Item should set "Default" and the NewEnum set -4 in Procedure Attributes.

Now it is OK.

Thanks.

Thanks for tracking the issue down. I was going around in circles trying to reproduce it!

Quote:

---

Originally Posted by dilettante

Thanks for tracking the issue down. I was going around in circles trying to reproduce it!

---

I roughly go through the codes, the parser is very intelligent and a complicated process but the code is very neat.

Have you made a XML parser so that we can make XLSX or XML by codes. (Somebody told me Excel 2007's xlsx file is actually compressed XML.)

I Rated 5 stars for your excellent coding.

Thanks.

While I do have a class for processing XML it only handles a form of "simplified XML." It is useful for me for a number of purposes where I have full control over the data sent between programs, but probably is not worth making available to others.

The main reason is that Microsoft's MSXML already handles XML very well, is present in any modern version of Windows (and even older ones with IE 5.0 or leter), and is much more complete. This makes it much safer for general use because it can parse and generate very complete XML.

Cheers, glad to help! I couldn't reproduce the problem for a while here either, then I just happen to notice the word "pasted" in Jonney's comment, and got suspicious...

Update.

I hope this new version didn't create new bugs while trying to fix old things.

Raised exceptions now all use custom error numbers instead of trying to use stabdard VB error numbers. These new numbers are all in the &H800499xx range.

The .Exists(key) method should now work for noth String "names" and numeric "index" values.

.Exists() is now called within Property Get Item(key) for keys passed as String names. This may help with a confusing situation where the case-sensitive names will throw you off if you try to use the "bang" syntax (! operator)... the IDE may adjust the case of typed in names on you, leading to trouble. Before this change calling Item() with a non-existant key name resulted in a very confusing exception being raised, and hopefully now you'll see a less confusing exception instead.

It all sort of comes down to the limitations of VB's Collection class. I may be tempted to do a 2.0 rewrite at some point using Scripting.Dictionary instead.

The JsonBag.rtf document has also been updated.

Hmm... I believe the current version is not compatible with 64-bit VB/Office VBA. I tried to tweak some of the Long/LongPtr definitions, but I don't understand well how the arithmetic works. Would it be safe to just mass-replace Long with LongLong and add PtrSafe for a 64-bit version only?

Quote:

---

Originally Posted by Xpucmo

Hmm... I believe the current version is not compatible with 64-bit VB/Office VBA. I tried to tweak some of the Long/LongPtr definitions, but I don't understand well how the arithmetic works. Would it be safe to just mass-replace Long with LongLong and add PtrSafe for a 64-bit version only?

---

64-bit VBA will require some modification. I wouldn't try to blindly replace one data type with another, but such changes will be required strategically in regard to the API calls that deal with pointers.

dilettante, anyway to modify the above code to read a Json that has two delimiters? As in this thread.

I used JsonBag 1.8. Received data as JSON

Code:

---

{"_comment":"pair_id: 166, interval: 86400, candles: 120",
"candles":[[1404777600000,1963.71,0],[1404864000000,1972.83,0],[1404950400000,1964.6801,0],[1405036800000,1967.5699,0],[1405296000000,1977.1,0],[1405382400000,1973.28,0],[1405468800000,1981.5699,0],[1405555200000,1958.12,0],[1405641600000,1978.22,0],[1405900800000,1973.63,0],[1405987200000,1983.53,0],[1406073600000,1987.01,0],[1406160000000,1987.98,0],[1406246400000,1978.34,0],[1406505600000,1978.91,0],[1406592000000,1969.95,0],[1406678400000,1970.0699,0],[1406764800000,1930.67,0],[1406851200000,1925.15,0]

---

The first figure in brackets ([1404777600000,1963.71,0]) - is the date. How do I get the date in the usual format: 24/12/2014 11:00 ?
thank you.

Quote:

---

Originally Posted by Vostok

The first figure in brackets ([1404777600000,1963.71,0]) - is the date. How do I get the date in the usual format: 24/12/2014 11:00 ?

---

Well you have to remember that JSON comes from the impoverished world of "web standards" and in particular JavaScript. JavaScript has no decent native data type for date-time values so people tend to pass almost anything around.

Commonly they'll use Unix Timestamp format, though nice folks will scale (divide) that by 1000 since millisecond precision is (a.) kind of silly and (b.) just means more text to ship over the wire.

See On the nightmare that is JSON Dates and just know you are not alone.


So here is a demo based on your sample JSON fragment.

I'm pretty sure the Unix-to-Earth (i.e. VB Date) conversion works correctly though I have not throoughly tested values from #1/19/2038 3:14:08 AM# onward.

Your sample values have no time-part, so here I have skipped over the step of converting the UTC result to the local time zone but I don't think that matters for your case. Additional functions to convert to local and to UTC are included, just not used here.


The demo converts the timestamps to VB Date type values, then inserts them back as formatted String values just for visual examination. Remember, JavaScript and thus JSON are weak in this area and cannot use Date values, so only String values could be used. In a real program you'd probably just convert and use the values and not stick them back into the JsonBag as this demo does.

Attachment 122093

Quote:

---

Originally Posted by Nightwalker83

dilettante, anyway to modify the above code to read a Json that has two delimiters? As in this thread.

---

Sorry, I hadn't seen your post before today.

Well wrapping a few completed instances of his text in proper bracketing JsonBag didn't have any problem handling it:

Attachment 122097

Quote:

---

Originally Posted by dilettante

The demo converts the timestamps to VB Date type values, then inserts them back ....

---

Thank you for your response to my question. The procedure is called "test_HD_TimeFrame", module name's Download_TF. My operating system Windows XP. File is sent. Thank you.

P.S. Like everything worked as it should. I was not wrong in the code?

Code:

---

Debug.Print UnixToDate(CDec(1411430400000#))
Debug.Print ToLocal(UnixToDate(CDec(1411430400000#)))
Debug.Print ToUTC(UnixToDate(CDec(1411430400000#)))

'[1410998400000,2003.0699,2012.34,2003.0699,2011.36,0]
'23.09.2014
'23.09.2014 11:00:00
'22.09.2014 13:00:00

---

Your tests look good.

Just be wary about using Double values (such as 1411430400000#) because they have limited precision.

Decimal is safer for this, but we have no way to enter a Decimal literal in code. I used Currency (1411430400000@) because it has greater precision.

See Data Type Summary for details.

Quote:

---

Originally Posted by dilettante

... I used Currency (1411430400000@) because it has greater precision.

---

Perhaps I will follow your example and do the same code as your own. Thank you.

Major update.

Changed enumeration model, optimized parsing somewhat, minor new features, untested support for VBA7/WIN64 VBA7. See JsonBag.rtf for more information.

Very simple Excel example included, which bulks up the attachment quite a bit.

Still largely compatibile with programs using earlier versions unless they were enumerating Names.

Thanks for this. I recently discovered JsonBag and its a good fit for adding json support to some of our legacy vb6 projects. I especially like how easy it is to build the object in code, and then serialize it and send it on its way. It's also nice that there are no other dependencies outside of JsonBag.cls

Thanks!

Ron

This is a helpfuly module. Where can we get a license?

We're using ver. 1.8.j1. In order to use it, I was told by our attorney that we need to have a license (something more tangible than posting to a publicly available forum because the original author still retains the copyright).

Ok, here is a bundle of just the Class module and documentation. Versions 1.7, 1.8, and 2.0 included (there was no 1.9 and 1.6 was pretty rough).

Apache 2.0 License added. Should be liberal enough for most purposes. ;)

New version 2.1, should be compatibile with most programs written against 2.0 and remains largely compatible with those written against earlier versions.

I should probably note that the whitespace-related properties are really only useful for producing formatted "dumps" of JSON for easier development, testing, debugging, and documenting. Parsing always ignores whitespace and adding whitespace just creates larger JSON payloads so production programs shouldn't do it.

Changes for 2.1

Even though just a "point release" this version makes quite a large number of changes. However it should be highly compatible with version 2.0 and most programs should not require changes to accomodate these JsonBag changes:

• Minor optimization of Public Property Get Item (Exists() calls now create the "prefixed" keys used internally).
• Other small optimizations.
• New Clone() method returns a deep-copy of the original JsonBag.
• New CloneItem read/write Property, like Item but deep-copies instead of returning/storing the original Object reference.
• New ItemIsJSON read-only Property, used to determine whether an item is a JsonBag rather than a simple value.
• New ItemJSON read/write Property, like Item but accepts and returns JSON text for JsonBag nodes.
• Property Let Item/CloneItem will only allow VB6 Objects of type JsonBag to be assigned (i.e. this is now validated).
• Bug fix: Parsing (Property Let JSON) did not detect "arrays" and "objects" with missing values between commas.
• Bug fix: Replacing an "array" item at the end of the "array" caused "Subscript out of range" error 9.
• Bug fix: Property Let JSON did not propagate .WhitespaceNewLine to child nodes as they were inserted.
• Bug fix: Methods AddNewArray() and AddNewObject() did not propagate .DecimalMode, .Whitespace, .WhitespaceIndent, or .WhitespaceNewLine to child nodes as they were inserted.
• Bug fix: Clear method did not reset .IsArray. Now it gets cleared to the default value False. This change might be the most likely one to have impact on existing programs relying on earlier behavior.
• New conditional compilation constant: NO_DEEPCOPY_WHITESPACE. When True the .WhitespaceXXX properties are not propagated, which improves parsing performance.

Notes:

The whitespace propagation bug fixes resulted in a significant drop in parsing performance. However getting things right is probably more important. Massive JSON strings are not used very often, nor is heavy batch processing of vast numbers of smaller JSON strings. But as mentioned above in the changes list, NO_DEEPCOPY_WHITESPACE has been provided where the tradeoff in functionality for better performance makes sense.

Setting the VB6 compiler's native code optimization switches has little impact on JsonBag's parsing performance.


General:

Bug reports are always appreciated.

The new "cloning" operations would probably only be used in programs performing complex operations on JsonBags. That would be those needing to trim out parts of the tree as separate JsonBag writeable objects, copy nodes from one JsonBag to another, etc. Without cloning, making a change to such JsonBag nodes alters the single instance that multiple JsonBags would point to by Object references.

The ItemJson property is more of a convenience feature. It makes it easy to build a JsonJag partially from JSON fragements for the "constant" parts of a JSON payload being created. This could simplify client code by saving on calls to individual properties and methods just to insert such constant data. Previously a program might do quite a few calls or instead might create temporary JsonBags and assign JSON text to them via their JSON properties before adding them to the "main" JsonBag.

The attachment is bulky because of the Excel workbook included, which is just a tiny VBA usage demo.