ObjectToJSON: TDateTime and MongoDB

BBackSoon · 2015-03-03 15:37:09

Hello there!

Suppose you have an object that looks like this:

  TIPRec = class(TPersistent)
  private
    f_id: RawUTF8;
    fIPAddr: AnsiString;
    fTimeStamp: TDateTime;
  public
    constructor Create;
    destructor Destroy; override;
  published
    property _id: RawUTF8 read f_id write f_id;
    property IPAddr: AnsiString read fIPAddr write fIPAddr;
    property TimeStamp: TDateTime read fTimeStamp write fTimeStamp;
  end;

In my code I call ObjectToJSON to serialize it to JSON and then insert it into my MongoDB like this:

var
  rutf: RawUTF8;
  item: TIPRec;
begin
  item := TIPRec.Create;
  try
    try
      item.IPAddr := IP;
      item.TimeStamp := now;
      //--
      rutf := ObjectToJSON(item, [woHumanReadable]);;
      fIPCollection.Insert(rutf, []);
      Result := true;
    except
      Result := false;
    end;
  finally
    FreeAndNil(item);
  end;
end;

After inserting the record I open RoboMongo to check if the insertion was successful, and actually the document is there in my collection.... but... the TimeStamp field (which was of type TDateTime) is now of type STRING. Therefore I cannot query it as I would query a date field.

I suspect that ObjectToJSON doesn't serialize TDateTime fields in a MongoDB-friendly way. Is there any way to accomplish a JSON serialization of my object that would tell MongoDB to consider TDateTime fields as per what they really are?
THANK YOU!

ab · 2015-03-03 19:37:46

If you use the ORM, mORMotMongoDB.pas will recognize TSQLRecord TDateTime fields and store them as
By default, ObjectToJSON() serialize TDateTime fields as ISO-8601 text.
Current implementation did not let such ISO-8601 text be recognized when converted into TDocVariant documents, before sending to MongoDB...

Please try http://synopse.info/fossil/info/54b151f6ea
I introduced the new woDateTimeWithMagic setting for TTextWriterWriteObjectOption, now recognized as such in SynMongoDB.
You could use this new option for your purpose.
Now ObjectToJSON(...,[woDateTimeWithMagic]) should allow to send TDateTime as betDateTime BSON values.
Feedback is welcome!

BBackSoon · 2015-03-04 23:27:35

I'll try it. Thank you.
Awesome project this mORMot! Kudos!!

BBackSoon · 2015-03-04 23:57:12

Actually it does work, but.... now seems like its counterpart JSONToObject doesn't interpret those dates correctly when I READ the object back from my MongoDB and I want to assign it back to my native object.
I think TJSONToObjectOption needs a [j2oDateTimeWithMagic] option as well... otherwise I can store my objects in MongoDB, but I cannot re-load them from the DB back into my original Delphi objects...

ab · 2015-03-05 08:15:04

JSONToObject() should handle ISO-8601 text into a TDateTime as expected:

      tkFloat:
        if P^.PropType{$ifndef FPC}^{$endif}=TypeInfo(TDateTime) then
          if wasString then begin
            if PInteger(PropValue)^ and $ffffff=JSON_SQLDATE_MAGIC then
              inc(PropValue,3); // ignore U+FFF1 pattern 
            P^.SetFloatProp(Value,Iso8601ToDateTimePUTF8Char(PropValue,0));
          end else
            exit else
         ....

Of course, for this to work, the format should be modNoMongo when creating the JSON from

In all cases, we may benefit, in mORMotMongoDB.pas, to implement ObjectToBSON() and BSONToObject() features, which would avoid the conversion to/from JSON.
I've created a feature request: http://synopse.info/fossil/tktview/2cf4f37c74a5

BBackSoon · 2015-03-05 17:55:04

Wait... I'm not sure I understand here. This is what I do:
- I use ObjectToJSON with [woDateTimeWithMagic] to create a JSON that I can insert into MongoDB
- Later on I query the MongoDB and use the JSONtoObject to restore the object I read from MongoDB into a new object
So of course the JSON is in Mongo format, because I receive such JSON by querying the MongoDB...
How can I do that and correctly convert dates back and forth?

ab · 2015-03-06 08:06:48

Try to explicitly get the JSON in modNoMongo format.

BBackSoon · 2015-03-06 19:09:10

Unless I'm very wrong here... something doesn't work as I thought.

Here's the method I'm using:

rutf := fStatsColl.FindJSON('', '', 1, 0, [], modNoMongo);

And after calling this method, here's the contents of the rutf variable:

'{"_id":"54F79A32F6A1078C08487DE7","FirstStart":{"$date":"2015-03-06T19:03:35"}, ...........

See? The $date Mongo-date-field identifier is still there.

ab · 2015-03-06 22:49:57

Did you find out why?

BBackSoon · 2015-03-07 03:56:45

Nope. I was reporting it to you cause that's what I did and I expected it to work... but that's the result I got.
Any clues?

ab · 2015-03-07 09:52:28

Use the debugger to see why the format parameter is not propagated.

BBackSoon · 2015-03-07 14:52:28

Here's where you lose it:

function TMongoConnection.GetJSONAndFree(Query: TMongoRequestQuery; Mode: TMongoJSONMode): RawUTF8;
var W: TTextWriter;
    ReturnAsJSONArray: boolean;
begin
  ReturnAsJSONArray := Query.NumberToReturn>1;
  W := TTextWriter.CreateOwnedStream;
  try
    if ReturnAsJSONArray then
      W.Add('[');
    if Mode=modMongoShell then
      GetRepliesAndFree(Query,ReplyJSONExtended,W) else
      GetRepliesAndFree(Query,ReplyJSONStrict,W);
    W.CancelLastComma;
    if ReturnAsJSONArray then
      W.Add(']');
    W.SetText(result);
    if (result='') or (result='[]') or (result='{}') then
      result := 'null';
  finally
    W.Free;
  end;
end;

In this function you only check if Mode is equal to modMongoShell or not. Instead of that if..then there should be a:

    case Mode of
      modNoMongo: GetRepliesAndFree(Query,ReplyJSONNoMongo,W);
      modMongoStrict: GetRepliesAndFree(Query,ReplyJSONStrict,W);
      modMongoShell: GetRepliesAndFree(Query,ReplyJSONExtended,W);
    end;

The function ReplyJSONNoMongo was not existing, so I created it:

procedure TMongoConnection.ReplyJSONNoMongo(Request: TMongoRequest;
  const Reply: TMongoReplyCursor; var Opaque);
var W: TTextWriter absolute Opaque;
begin
  Reply.FetchAllToJSON(W,modNoMongo,false);
  W.Add(',');
end;

With these changes everything works perfectly.

BBackSoon · 2015-03-08 14:20:21

Are you going to commit my changes into your source code?

ab · 2015-03-08 19:22:37

Yes - but I was in a family Week End far away from my PC.

Should be OK with http://synopse.info/fossil/info/ac92b58ad4

Thanks for sharing!

mORMot Open Source

#1 2015-03-03 15:37:09

ObjectToJSON: TDateTime and MongoDB

#2 2015-03-03 19:37:46

Re: ObjectToJSON: TDateTime and MongoDB

#3 2015-03-04 23:27:35

Re: ObjectToJSON: TDateTime and MongoDB

#4 2015-03-04 23:57:12

Re: ObjectToJSON: TDateTime and MongoDB

#5 2015-03-05 08:15:04

Re: ObjectToJSON: TDateTime and MongoDB

#6 2015-03-05 17:55:04

Re: ObjectToJSON: TDateTime and MongoDB

#7 2015-03-06 08:06:48

Re: ObjectToJSON: TDateTime and MongoDB

#8 2015-03-06 19:09:10

Re: ObjectToJSON: TDateTime and MongoDB

#9 2015-03-06 22:49:57

Re: ObjectToJSON: TDateTime and MongoDB

#10 2015-03-07 03:56:45

Re: ObjectToJSON: TDateTime and MongoDB

#11 2015-03-07 09:52:28

Re: ObjectToJSON: TDateTime and MongoDB

#12 2015-03-07 14:52:28

Re: ObjectToJSON: TDateTime and MongoDB

#13 2015-03-08 14:20:21

Re: ObjectToJSON: TDateTime and MongoDB

#14 2015-03-08 19:22:37

Re: ObjectToJSON: TDateTime and MongoDB

Board footer