Access json objects using dot notation instead of parenthesis

[zaphod4254]

SO glad I found this! Saving me TONS of work!! THANK YOU!

This is more of an enhancement request.

I'm using this in Access on Windows only.

Given this simple json and code example:

sJsonText = "{""obj1"":{""prop1"":""value1"",""prop2"":""value2""},""obj2"":{""prop3"":""value3"",""prop4"":""value4""}}" Set oJson = ParseJSON(sJsonText) Debug.Print oJson("obj1")("prop2")

What would it take to change things so that we could reference the properties using dot notation instead of separating each key name in it it's own parenthesis and quotes. I.e.,

Debug.Print oJson("obj1.prop2")

A bit easier to type, and easier to read.

Maybe build it with an option to use either way, for backward compatibility?

I found this alternate, simplified code for VBA json parsing and it does it that way. But I'd rather use vba-json. I like the reencoding to text option that vba-json has too. https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a

[zaphod4254]

In hindsight, this may not be desirable. Looking at the code further for that other module, I finally figured out what he was doing.

He isn't actually creating true nested dictionary objects or arrays. He's creating ONE large single-level dictionary object and using string concatenation to create the keys of that one object using all the subkeys in any nested objects or arrays.

It does work, and it's nice to read and easier to type, but I suppose it depends on if we care whether the json object internally accurately reflects the true structure of the json formatted text. I'm not sure it matters, but it might.

That said, it would sure be nice to have something like this. Or better yet, be able to reference as simply oJson.obj1.prop2 (back to my original example), but I'm not sure if that's even technically possible.

Another thought I had was to have a helper "GetJsonValue" type of function that would act as a translator. So I could do something like Debug.Print GetJsonValue("oJson.obj1.prop2") In this short example it's not really that much less typing than having the properties in separate parentheses and quotes since it adds the function name, but in a very deep nested situation I think it would help.

[Bearx2004]

see this https://github.com/Bearx2004/vb6jsonx

[zaphod4254]

see this https://github.com/Bearx2004/vb6jsonx

Looks interesting. Unfortunately for my current use-case, I'm unable to use a 3rd party library. I need native VBA code that I can put directly into an Access module.

[Bearx2004]

see this https://github.com/Bearx2004/vb6jsonx

Looks interesting. Unfortunately for my current use-case, I'm unable to use a 3rd party library. I need native VBA code that I can put directly into an Access module.

Yes, but you can import the source code（ JsonObject.cls , JsonArray.cls ）into your Access module, I am a Chinese and my English is not good.

[zaphod4254]

Disclaimer: I'm not a professional coder myself. I'm trying to make something work for a department Access database we have. As I mention below, I have experience with json in PowerShell, and using that knowledge to help me try to get something implemented in Access VBA.

Thank you for the tip of importing the .cls files into an Access module. I will give that a try.

However, I'm unclear how this vb6jsonx code integrates with VBA-JSON.

My primary need is to convert json text received via a web API call into an object, then access specific known properties. I'm not creating json objects or text from scratch myself.

I don't need an entirely separate json class from what VBA-JSON already does. And that's what it appears to me that this vb6jsonx code is doing, creating its own class.

I simply want to take the object that VBA-JSON already creates based on the json text from the web API and have a more compact syntax to reference specific properties.

My comparison for this is working with json in Windows PowerShell. In PowerShell I can convert json text to an object and then reference individual properties in the object using simple dot notation, like JsonObject.property1.property2.property3.etc.

I do see that's what the .Query method does in this code, for its custom class, but I don't see how I'd take the object I already have from VBA-JSON and use it with this vb6jsonx code.

[houghtonap]

You might want to take a look at my reply to issue #186 where there is an example for Excel, but the mechanics are similar in Access. I use VBA-JSON in both Access and Excel for accessing API calls and pushing into Access or Excel for review or processing.

[Bearx2004]

Disclaimer: I'm not a professional coder myself. I'm trying to make something work for a department Access database we have. As I mention below, I have experience with json in PowerShell, and using that knowledge to help me try to get something implemented in Access VBA.

Thank you for the tip of importing the .cls files into an Access module. I will give that a try.

However, I'm unclear how this vb6jsonx code integrates with VBA-JSON.

My primary need is to convert json text received via a web API call into an object, then access specific known properties. I'm not creating json objects or text from scratch myself.

I don't need an entirely separate json class from what VBA-JSON already does. And that's what it appears to me that this vb6jsonx code is doing, creating its own class.

I simply want to take the object that VBA-JSON already creates based on the json text from the web API and have a more compact syntax to reference specific properties.

My comparison for this is working with json in Windows PowerShell. In PowerShell I can convert json text to an object and then reference individual properties in the object using simple dot notation, like JsonObject.property1.property2.property3.etc.

I do see that's what the .Query method does in this code, for its custom class, but I don't see how I'd take the object I already have from VBA-JSON and use it with this vb6jsonx code.

Example:

dim oJson as Dictionary dim sJsonText as String sJsonText = "{""obj1"":{""prop1"":""value1"",""prop2"":""value2""},""obj2"":{""prop3"":""value3"",""prop4"":""value4""}}" Set oJson = ParseJSON(sJsonText) Debug.Print oJson("obj1")("prop2")

If you added (JsonObject.cls,JsonArray.cls) in your Access modules, 1."how I'd take the object I already have from VBA-JSON and use it with this vb6jsonx code?" like here:

dim oJson as Dictionary dim sJsonText as String sJsonText = "{""obj1"":{""prop1"":""value1"",""prop2"":""value2""},""obj2"":{""prop3"":""value3"",""prop4"":""value4""}}" Set oJson = ParseJSON(sJsonText) dim oJsonObject as New JsonObject Set oJsonObject .NativeObject=oJson Debug.Print oJsonObject .Query("obj1.prop2")

'original access data like this： Debug.Print oJsonObject .NativeObject("obj1")("prop2")

Okey! you can see :JsonObject .NativeObject is a same Object as oJson

2."I'm unclear how this vb6jsonx code integrates with VBA-JSON?" like here:

dim oJsonObject as New JsonObject dim sJsonText as String sJsonText = "{""obj1"":{""prop1"":""value1"",""prop2"":""value2""},""obj2"":{""prop3"":""value3"",""prop4"":""value4""}}" Call oJsonObject .OfSON(sJsonText) Debug.Print oJsonObject .Query("obj1.prop2")

[zaphod4254]

You might want to take a look at my reply to issue #186 where there is an example for Excel, but the mechanics are similar in Access. I use VBA-JSON in both Access and Excel for accessing API calls and pushing into Access or Excel for review or processing.

Thanks for the info. I had already read that, and I DO fully understand that information and why the syntax is the way it currently is. And I do have code working to use VBA-JSON as-is. I just wasn't sure whether there might some other option in how the object is created to make the syntax easier for accessing nested properties. I at least wanted to ask. Perhaps creating a custom class? Or adding a helper query function that translates?

I do see that's what the .Query method does in this code, for its custom class, but I don't see how I'd take the object I already have from VBA-JSON and use it with this vb6jsonx code.

Example:

(snip)

If you added (JsonObject.cls,JsonArray.cls) in your Access modules, 1."how I'd take the object I already have from VBA-JSON and use it with this vb6jsonx code?" like here:

dim oJson as Dictionary dim sJsonText as String sJsonText = "{""obj1"":{""prop1"":""value1"",""prop2"":""value2""},""obj2"":{""prop3"":""value3"",""prop4"":""value4""}}" Set oJson = ParseJSON(sJsonText) dim oJsonObject as New JsonObject Set oJsonObject .NativeObject=oJson Debug.Print oJsonObject .Query("obj1.prop2")

Thanks! I think the "Set oJsonObject .NativeObject=oJson" is what I needed.