Export as JSON

I am not really too familar with JSON… so I thought I’d ask here.

I need to write a method that takes a recordset (the query that creates the recordset could be any number of things)
That record set then needs to be written to a text file in JSON format
Assuming my recordset has the following fields

  • ID
  • NAME

would the proper JSON format be :

    { "ID" : "0", 
      "DT_CREATED" : "201311220910", 
      "DT_UPDATED" : "", 
      "NAME" : "Dave", 
      "DESCRIPTION" : "", 
      "NUMBER" : "####-##-####", 
      "DATE_ISSUE" : "", 
      "NOTES" : "xxxxx" },
     { "ID" : "0", 
      "DT_CREATED" : "201311220910", 
      "DT_UPDATED" : "", 
      "NAME" : "Suzi", 
      "DESCRIPTION" : "", 
      "NUMBER" : "####-##-####", 
      "DATE_ISSUE" : "", 
      "NOTES" : "xxxxx" },

is EVERYTHING (data wise) always QUOTED (even numbers?)
Should a NULL value be stated as just empty double quotes or is there a special convention?
what about BLOB data?


[Note : I realize these dates are NOT in SQLDATE format… this is actually from some really OLD data, and just used as an example]

I am going to base my code on this forum topic


However my question about NULL and BLOB still apply.
And what is the best way to “escape” strings with double quotes inside (often asked question I know)

No, nothing is quoted but strings. JSON recognizes strings, booleans, doubles (and integers), objects, arrays, and null.

But there is no reason for you to hand-craft this as Xojo gives you two different ways. The easiest is the new framework:

dim recs() as auto

while not rs.EOF
  dim rec as new Xojo.Core.Dictionary
  for i as integer = 1 to rs.FieldCount
    dim v as variant = rs.IdxField( i ).Value
    if v isa Date then
      v = v.DateValue.SQLDateTime
    end if
    rec.Value( rs.IdxField( i ).Name ) = v
  recs.Append rec
  rs.MoveNext // Whoops, forgot that

dim out as text = Xojo.Data.GenerateJSON( recs )

I haven’t tested this exact code but you get the idea.

Edit: Added rs.MoveNext.

AT least not according to the simplified BNF as JSON.ORG

NULL can be written as null
Kem has done a right example so using his example if you want to explicit a NULL value you should write

BLOB cannot be used directly (it’s a text format) so you have to encode64 them and decode64 when you read it