Storing other objects in cJobject
So this means that I needed to store any object (not just stringifiable ones) as the value of a key/value pair in a cJobject.
For the sake of this example, we’ll populate a sheet and store some Excel Sheet objects in a cJobject. Normally I don’t need to do this since I always use data abstraction when interacting with sheets (see How to use cDataSet) and that has built in methods for converting to and from cJobjects and JSON, but for the sake of an example, let’s try this. We’ll use the test data from cJobject deep dive and write it to a sheet.
First the headings – we’ll call the columns the same thing as the keys of the JSON data.
Dim r As Range Set r = Range("Sheet1!a1") ' write the headings For Each jm In job.children(1).children r.Offset(, jm.childIndex - 1).value = jm.key Next jm
Now the data
For Each jp In job.children For Each jm In jp.children r.Offset(jp.childIndex, jm.childIndex - 1).value = jm.value Next jm Next jp
That correctly gives us this
But there’s no guarantee that the properties of each of the objects in the array are each in the same order if they came from some external JSON source, so we need to refine things a little.
Storing objects as values
Now we’ll create a new cJobject in which we store the Excel Range object at which we stored each column heading. Each property in this cJobject will have a key of the column name. Like this we can retrieve the address of the column header later when we process the data
' write the headings Set headings = New cJobject With headings.init(Nothing) For Each jm In job.children(1).children headings.add jm.key, r.Offset(, jm.childIndex - 1) headings.getObject(jm.key).value = jm.key Next jm End With
When we use the .add method, cJobject will notice that its being asked to store an object rather than a value and treat it specially internally. Here’s what headings looks like when stringified.
Of course an Excel range is not stringifiable to JSON, so instead it reports its type against the value. Note that you can store any kind of object or custom class – even another cJobject. It will treat them all the same way.
There are a few methods specially for dealing with objects stored this way –
These are equivalent
set obj = job.child("name").getObject() set obj = job.getObject("name")
if (job.child("name").isObjValue) then...
if (job.isObjValue("name")) then ....
Modifying an object value
When you use .add it replaces the current property value, or creates a new one as appropriate. Objects are the same as regular values. This examples will all create or replace the properties mentioned
job.add "key", value job.add "key.subkey", value job.add "key", someobject
Getting back to the example
So now we have the ranges that we stored the columns names in, and all the properties of that object are open to us.
Let’s take a look
For Each jp In headings.children Debug.Print jp.key, jp.getObject().Address Next jp
name $A$1 age $B$1
That means we can use the keys in the data to find the correct column to store each data value against by referencing the range object stored in the cJobject
' now the data For Each jp In job.children For Each jm In jp.children headings.getObject(jm.key).Offset(jp.childIndex).value = jm.value Next jm Next jp
which gives us the correct result
To test, we’ll shuffle the order of one of the objects in the test data like this
In our first example, the data for mary would have ended up in the wrong column, but since we are now storing the column range keyed by property name, we’ll always end up in the right place regardless of the incoming order. And here’s the result
Ad hoc classes
Using cJobject means that I can get many of the benefits of classes without bothering to make a class, as well as JSON and recursion by default for every data structure and the ability to create objects on the fly.
Now let’s look at some more cJobject topics.
For more on this see How to use cJobject