mORMot Open Source

BBackSoon

Member

Registered: 2014-11-15

Posts: 41

ObjectToJSON: TDateTime and MongoDB

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

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ObjectToJSON: TDateTime and MongoDB

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

Member

Registered: 2014-11-15

Posts: 41

Re: ObjectToJSON: TDateTime and MongoDB

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

BBackSoon

Member

Registered: 2014-11-15

Posts: 41

Re: ObjectToJSON: TDateTime and MongoDB

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

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ObjectToJSON: TDateTime and MongoDB

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

Member

Registered: 2014-11-15

Posts: 41

Re: ObjectToJSON: TDateTime and MongoDB

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

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ObjectToJSON: TDateTime and MongoDB

Try to explicitly get the JSON in modNoMongo format.

BBackSoon

Member

Registered: 2014-11-15

Posts: 41

Re: ObjectToJSON: TDateTime and MongoDB

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

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ObjectToJSON: TDateTime and MongoDB

Did you find out why?

BBackSoon

Member

Registered: 2014-11-15

Posts: 41

Re: ObjectToJSON: TDateTime and MongoDB

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

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ObjectToJSON: TDateTime and MongoDB

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

BBackSoon

Member

Registered: 2014-11-15

Posts: 41

Re: ObjectToJSON: TDateTime and MongoDB

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

Member

Registered: 2014-11-15

Posts: 41

Re: ObjectToJSON: TDateTime and MongoDB

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

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ObjectToJSON: TDateTime and MongoDB

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!