B4J Question JSON parsing confusion

[Kevin Hartin]

Hi again,

I am trying to sync some data from a website database to my B4X app (currently B4j but will be B4i).

The database table looks like;

I am using php to create a JSON package that looks like;

{
    "meeting_users": [
    {
        "ID": "100001",
        "MeetingID": "100001",
        "UserID": "10002",
        "MeetingUserStatus": "ACTIVE",
        "TS": "2021-02-13 19:15:37"
    }, {
        "ID": "100002",
        "MeetingID": "100001",
        "UserID": "10001",
        "MeetingUserStatus": "ACTIVE",
        "TS": "2021-02-12 14:30:18"
    }, {
        "ID": "100003",
        "MeetingID": "100001",
        "UserID": "10003",
        "MeetingUserStatus": "INACTIVE",
        "TS": "2021-02-12 15:51:33"
    }, {
        "ID": "100004",
        "MeetingID": "100002",
        "UserID": "10002",
        "MeetingUserStatus": "ACTIVE",
        "TS": "2021-04-10 12:59:38"
    }, {
        "ID": "100005",
        "MeetingID": "100001",
        "UserID": "10005",
        "MeetingUserStatus": "INACTIVE",
        "TS": "2021-02-13 19:15:27"
    }, {
        "ID": "100006",
        "MeetingID": "100001",
        "UserID": "10006",
        "MeetingUserStatus": "INACTIVE",
        "TS": "2021-02-13 19:15:28"
    }, {
        "ID": "100007",
        "MeetingID": "100001",
        "UserID": "10004",
        "MeetingUserStatus": "INACTIVE",
        "TS": "2021-02-13 19:15:30"
    }, {
        "ID": "100008",
        "MeetingID": "100002",
        "UserID": "10001",
        "MeetingUserStatus": "ACTIVE",
        "TS": "2021-02-12 17:52:19"
    }, {
        "ID": "100009",
        "MeetingID": "100002",
        "UserID": "10004",
        "MeetingUserStatus": "INACTIVE",
        "TS": "2021-02-12 18:43:09"
    }
    ]
}

The root object is the TableName and it has an array of objects representing each record. I think this is how it is supposed to be.

using the following B4j code I can parse the JSON package and extract the root object (TableName) into a list.

            Dim parser As JSONParser
            parser.Initialize(Job.GetString)
            Dim json As Map = parser.NextObject
            Dim recs As List = json.Get(Table)
            For i = 0 To recs.Size - 1
                Log("recs: "&i&" " & recs.Get(i))
            Next

Then when I loop through the list I get the following log output, which looks like each record object in each List member;

recs: 0 {MeetingUserStatus=ACTIVE, UserID=10002, MeetingID=100001, ID=100001, TS=2021-02-13 19:15:37}
recs: 1 {MeetingUserStatus=ACTIVE, UserID=10001, MeetingID=100001, ID=100002, TS=2021-02-12 14:30:18}
recs: 2 {MeetingUserStatus=INACTIVE, UserID=10003, MeetingID=100001, ID=100003, TS=2021-02-12 15:51:33}
recs: 3 {MeetingUserStatus=ACTIVE, UserID=10002, MeetingID=100002, ID=100004, TS=2021-04-10 12:59:38}
recs: 4 {MeetingUserStatus=INACTIVE, UserID=10005, MeetingID=100001, ID=100005, TS=2021-02-13 19:15:27}
recs: 5 {MeetingUserStatus=INACTIVE, UserID=10006, MeetingID=100001, ID=100006, TS=2021-02-13 19:15:28}
recs: 6 {MeetingUserStatus=INACTIVE, UserID=10004, MeetingID=100001, ID=100007, TS=2021-02-13 19:15:30}
recs: 7 {MeetingUserStatus=ACTIVE, UserID=10001, MeetingID=100002, ID=100008, TS=2021-02-12 17:52:19}
recs: 8 {MeetingUserStatus=INACTIVE, UserID=10004, MeetingID=100002, ID=100009, TS=2021-02-12 18:43:09}

The question is, How do I get the individual records objects into a map so I can use the Key and Value? I am sure it is dead simple, but the fog has descended...

I need to construct an SQL query for each record to INSERT or UPDATE the local SQLite DB record using ID as the Primary key, which I have already got the logic for, I just cant get the Key Value pairs at a record level.

As I want to use this code in a Sub that has the TableName as an input, does a https POST to get the JSON package, the Field Names (Keys) need to be pulled out of the Map, rather than to have any sort of fixed mapping of Values to variables.

Thanks,
Kev

 

[alwaysbusy]

Dim rec as map = recs.get(i) in the for loop.

 

[Kevin Hartin]

Thanks, I had tried something like that, but messed up something.

It is all working now, complete with the SQL logic to sync between mobile and the website.

Kev

 

[mcqueccu]

Using this online tool for B4X http://basic4ppc.com:51042/json/index.html you will get something like this to easy integrate in your code

Dim parser As JSONParser
parser.Initialize(job.getstring)
Dim root As Map = parser.NextObject
Dim meeting_users As List = root.Get("meeting_users")
For Each colmeeting_users As Map In meeting_users
 Dim MeetingUserStatus As String = colmeeting_users.Get("MeetingUserStatus")
 Dim UserID As String = colmeeting_users.Get("UserID")
 Dim MeetingID As String = colmeeting_users.Get("MeetingID")
 Dim ID As String = colmeeting_users.Get("ID")
 Dim TS As String = colmeeting_users.Get("TS")
Next