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
  • DT_CREATED
  • DT_UPDATED
  • NAME
  • DESCRIPTION
  • NUMBER
  • DATE_ISSUE
  • NOTES

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?

Thanks!

[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

https://forum.xojo.com/23690-how-to-generate-multiple-json-data/0#p197533

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
  next
  recs.Append rec
  rs.MoveNext // Whoops, forgot that
wend

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

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

Edit: Added rs.MoveNext.

No
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
rec.value(rs.idxField(i).name)=nil

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