Difference between JSONItem and Xojo.Data.GenerateJSON

The following classic code generates the below JSON

[code]sql = "SELECT * FROM tbl_emp_basic "
rs = Appdb.SQLSelect(sql)

dim jsonEmployees as new JSONItem
dim emp as new Dictionary

while not rs.eof
emp = new Dictionary
emp.Value(“emp_id”) = rs.Field(“emp_id”).StringValue
emp.Value(“emp_number”) = rs.Field(“emp_number”).StringValue
emp.Value(“emp_firstname”) = rs.Field(“emp_firstname”).StringValue
emp.Value(“emp_middleinitial”) = rs.Field(“emp_middleinitial”).StringValue
emp.Value(“emp_lastname”) = rs.Field(“emp_lastname”).StringValue
emp.Value(“emp_prefix”) = rs.Field(“emp_prefix”).StringValue
emp.Value(“emp_suffix”) = rs.Field(“emp_suffix”).StringValue
emp.Value(“emp_maiden_name”) = rs.Field(“emp_maiden_name”).StringValue
emp.Value(“emp_sex”) = rs.Field(“emp_sex”).StringValue
emp.Value(“emp_birthday”) = rs.Field(“emp_birthday”).StringValue

jsonEmployees.Value(str(intCount)) = emp

rs.MoveNext
intCount = intCount + 1
wend

rs.Close

dim jsonResults as new JSONItem
jsonResults.Value(“GetEmployees”) = jsonEmployees
[/code]

{"GetEmployees":{"1":{"emp_id":"0001110562","emp_number":"0001","emp_firstname":"PAULA","emp_middleinitial":"U","emp_lastname":"TAXES","emp_prefix":"MRS","emp_suffix":"","emp_maiden_name":"SATURN","emp_sex":"","emp_birthday":"1967-01-01"},"2":{"emp_id":"0002030169","emp_number":"0002","emp_firstname":"RHONDA","emp_middleinitial":"B","emp_lastname":"PLANET","emp_prefix":"MRS","emp_suffix":"","emp_maiden_name":"JUPITER","emp_sex":"","emp_birthday":"1962-01-01"},"3":{"emp_id":"0003091774","emp_number":"0003","emp_firstname":"JENNIFER","emp_middleinitial":"C","emp_lastname":"SIDEWALK","emp_prefix":"MISS","emp_suffix":"","emp_maiden_name":"","emp_sex":"","emp_birthday":"1971-01-01"},"4":{"emp_id":"0004091886","emp_number":"0004","emp_firstname":"KRISTA","emp_middleinitial":"J","emp_lastname":"AIRPORT","emp_prefix":"MISS","emp_suffix":"","emp_maiden_name":"","emp_sex":"","emp_birthday":"1989-01-01"},"5":{"emp_id":"0005100893","emp_number":"0005","emp_firstname":"AMIE","emp_middleinitial":"E","emp_lastname":"AIRPLANE","emp_prefix":"MISS","emp_suffix":"","emp_maiden_name":"","emp_sex":"","emp_birthday":"1999-01-01"},"6":{"emp_id":"0006022654","emp_number":"0006","emp_firstname":"MEREDITH","emp_middleinitial":"L","emp_lastname":"ZOO","emp_prefix":"MRS","emp_suffix":"","emp_maiden_name":"MARS","emp_sex":"","emp_birthday":"1957-01-01"},"7":{"emp_id":"0007071180","emp_number":"0007","emp_firstname":"TODD","emp_middleinitial":"R","emp_lastname":"BANK","emp_prefix":"MR","emp_suffix":"","emp_maiden_name":"","emp_sex":"","emp_birthday":"1984-01-01"},"8":{"emp_id":"0008120694","emp_number":"0008","emp_firstname":"RON","emp_middleinitial":"S","emp_lastname":"AUTOMOBILE","emp_prefix":"MR","emp_suffix":"","emp_maiden_name":"","emp_sex":"","emp_birthday":"1998-01-01"},"9":{"emp_id":"0009062678","emp_number":"0009","emp_firstname":"EDWARD","emp_middleinitial":"E","emp_lastname":"ELEVATOR","emp_prefix":"MR","emp_suffix":"","emp_maiden_name":"","emp_sex":"","emp_birthday":"1974-01-01"},"10":{"emp_id":"0010021664","emp_number":"0010","emp_firstname":"ROBERT","emp_middleinitial":"N","emp_lastname":"SHOEHORN","emp_prefix":"MR","emp_suffix":"","emp_maiden_name":"","emp_sex":"","emp_birthday":"1961-01-01"}}}

I need to be able to create identical JSON on IOS. I cannot seem to do so. The below code creates JSON that looks like below. Can anyone give me a suggestion on how to generate identical JSON?

[code]Dim sql As Text
sql = “SELECT * FROM tbl_employee”

data = App.DB.SQLSelect(sql)
If Not Data.EOF Then

While Not data.EOF
dim dbEntryDict as New Dictionary
dbEntryDict.Value(“emp_id”)=data.Field(“emp_id”).TextValue
dbEntryDict.Value(“emp_number”)=data.Field(“emp_number”).TextValue
dbEntryDict.Value(“emp_lastname”)=data.Field(“emp_lastname”).TextValue
dbEntryDict.Value(“emp_firstname”)=data.Field(“emp_firstname”).TextValue
dbEntryDict.Value(“emp_middleinitial”)=data.Field(“emp_middleinitial”).TextValue
dbEntryDict.Value(“emp_prefix”)=data.Field(“emp_prefix”).TextValue
dbEntryDict.Value(“emp_suffix”)=data.Field(“emp_suffix”).TextValue
dbEntryDict.Value(“emp_maiden_name”)=data.Field(“emp_maiden_name”).TextValue
dbEntryDict.Value(“emp_sex”)=data.Field(“emp_sex”).TextValue
dbEntryDict.Value(“emp_birthday”)=data.Field(“emp_birthday”).TextValue
dbArray.Append(dbEntryDict)
data.MoveNext
Wend
data.Close

Else
dim dbEntryDict as New Dictionary
dbEntryDict.Value(“json_data”)=“Nothing”
dbArray.Append(dbEntryDict)
End If

Dim d as New Dictionary
d.Value(“GetEmployees”)=dbArray

json=GenerateJSON(d)
[/code]

{"PutEmployees":[{"emp_birthday":"1967-01-01","emp_firstname":"PAULA","emp_id":"0001110562","emp_lastname":"TAXES","emp_maiden_name":"SATURN","emp_middleinitial":"U","emp_number":"0001","emp_prefix":"MRS","emp_sex":"","emp_suffix":""},{"emp_birthday":"1961-01-01","emp_firstname":"ROBERT","emp_id":"0010021664","emp_lastname":"SHOEHORN","emp_maiden_name":"","emp_middleinitial":"N","emp_number":"0010","emp_prefix":"MR","emp_sex":"","emp_suffix":""},{"emp_birthday":"1962-01-01","emp_firstname":"RHONDA","emp_id":"0002030169","emp_lastname":"PLANET","emp_maiden_name":"JUPITER","emp_middleinitial":"B","emp_number":"0002","emp_prefix":"MRS","emp_sex":"","emp_suffix":""},{"emp_birthday":"1971-01-01","emp_firstname":"JENNIFER","emp_id":"0003091774","emp_lastname":"SIDEWALK","emp_maiden_name":"","emp_middleinitial":"C","emp_number":"0003","emp_prefix":"MISS","emp_sex":"","emp_suffix":""},{"emp_birthday":"1989-01-01","emp_firstname":"KRISTA","emp_id":"0004091886","emp_lastname":"AIRPORT","emp_maiden_name":"","emp_middleinitial":"J","emp_number":"0004","emp_prefix":"MISS","emp_sex":"","emp_suffix":""},{"emp_birthday":"1999-01-01","emp_firstname":"AMIE","emp_id":"0005100893","emp_lastname":"AIRPLANE","emp_maiden_name":"","emp_middleinitial":"E","emp_number":"0005","emp_prefix":"MISS","emp_sex":"","emp_suffix":""},{"emp_birthday":"1957-01-01","emp_firstname":"MEREDITH","emp_id":"0006022654","emp_lastname":"ZOO","emp_maiden_name":"MARS","emp_middleinitial":"L","emp_number":"0006","emp_prefix":"MRS","emp_sex":"","emp_suffix":""},{"emp_birthday":"1984-01-01","emp_firstname":"TODD","emp_id":"0007071180","emp_lastname":"BANK","emp_maiden_name":"","emp_middleinitial":"R","emp_number":"0007","emp_prefix":"MR","emp_sex":"","emp_suffix":""},{"emp_birthday":"1998-01-01","emp_firstname":"RON","emp_id":"0008120694","emp_lastname":"AUTOMOBILE","emp_maiden_name":"","emp_middleinitial":"S","emp_number":"0008","emp_prefix":"MR","emp_sex":"","emp_suffix":""},{"emp_birthday":"1974-01-01","emp_firstname":"EDWARD","emp_id":"0009062678","emp_lastname":"ELEVATOR","emp_maiden_name":"","emp_middleinitial":"E","emp_number":"0009","emp_prefix":"MR","emp_sex":"","emp_suffix":""}]}

Without getting too far into it, I see the first obvious difference is that you are using an array in the bottom code but only objects with numbers as keys in the top code.

I couldn’t determine how to do it with objects on IOS like done with JSONItem in the classic. Any suggestions or a quick example on how to do that? JSON is clearly not my expertise :smiley:

Object = Dictionary, Array = well, an array. In the bottom code, change dbArray from an array to a Dictionary and assign values using an index as text as you do in the top code through intCount.

Sometimes you just need to have another set of eyes look at your code to point out the obvious! Thanks Kem!