Extract just one value from json array result in vb6

Question

The result of json that I received from the SMS sending panel by Rest API is as follows and display in textbox :

{
"status": "OK",
"code": "OK",
"message": "Ok",
"data": {
"messages": [
  {
    "number": "+9710001529",
    "message": "Hello World",
    "sender": "+97911308600",
    "time": "2022-07-12T20:12:14Z",
    "type": "normal"
  },
  {
    "number": "+9710001529",
    "message": "Just For Test",
    "sender": "+979051931024",
    "time": "2022-06-28T23:15:22Z",
    "type": "normal"
  },
  {
    "number": "+9710001529",
    "message": "Test",
    "sender": "+979565547989",
    "time": "2022-01-28T16:04:50Z",
    "type": "mobilepanel"
  },
    {
    "number": "+9710001529",
    "message": "Comment",
    "sender": "+979102900089",
    "time": "2018-06-16T22:23:23Z",
    "type": "normal"
  }
]
},
"meta": {
"total": 37,
"pages": 4,
"limit": 10,
"page": 0,
"prev": null,
"next": "http://0.0.0.0:80/v1/inbox?limit=10\u0026page=1"
}
}

Now, I need to fetch the first mobile number with the name "sender" and show it in textbox for searching in database. The result should look like this: +97911308600.

I downloaded VB-JSON, VB6 JSON Parser Class Library and try to get a specific field from JSON data structure. if json result was not array like this code works good:

{
"status": "OK",
"code": "OK",
"message": "Ok",
"data": {
"credit": 2655946.6574392905
}
}

my code :

Dim p As Object
Set p = json.parse(Text1.text)
Debug.Print p.Item("data").Item("credit")

My expected output :

 2655946.6574392905

The problem is when the Json result is a collection of arrays. How can I read first "sender" value as Mobile number just like value of "credit"?

Please guide me or post code. Thank you

Answer 1

Short answer:

o.item("data").item("messages").item(1).item("number")

Easy way to find out..

1. Put a breakpoint right after the .Parse(...) call and then stop your execution and proceed to the "Immediate Window".
2. Start debugging using the TypeName(...) method from VB6.

?TypeName(o) => Dictionary
?TypeName(o.item("data")) => Dictionary
?TypeName(o.item("data").item("messages")) => Collection

So, this is how the parser renders the array of objects. Instead of a Dictionary, it is a collection. Collections can be indexed via numeric keys (base 1).

Thus,

?TypeName(o.item("data").item("messages").item(1)) => Dictionary

Now, we're back to a Dictionary, so we're back on a JSON object as opposed to an array. You could also have done .Count on the Collection if you needed to iterate.

And, then, finally, extract the field from the collection you wanted:

o.item("data").item("messages").item(1).item("number") => +9710001529

And, you're there.

Answer 2

Try using tiny mdJson.bas module from this thread instead of some bloated components and libraries.

It's a single file JSON parser implementation that uses nested VBA.Collections to represent JSON objects and arrays and uses JSON Path expressions to access nested data.

Your code would become this

Dim p As Object
Set p = JsonParseObject(Text1.text)
Debug.Print JsonValue(p, "data/credit")

You can read the first sender like this

Debug.Print JsonValue(p, "messages/0/sender")

. . . or using JSON Path expressions like this

Debug.Print JsonValue(p, "$.messages[0].sender")