Inserting Datetime field using ES Nest

Elastic Stack Elasticsearch
1

I'm trying to add a field which takes the system's datetime (DateTime.Now) and index it on ES server.
When I insert it without any custom format, I can index the documents. But if I mention any format, it is throwing an error. can some one help me to correct ?

my mappings:

 var createIndexResponse =
                           client.CreateIndex(defaultIndex, c => c
                           .Mappings(m => m
                           .Map<Document>(mp => mp
                           .Properties(ps => ps
                               .String(s => s.Name(e => e.Title))
                               .Date(dt=>dt.Name(en=>en.CreatedDate))
                               . //other fields

If I add something like this, .Date(dt=>dt.Name(en=>en.CreatedDate).Format("yyyy-MM-dd HH:mm:ss))" then it is throwing an error.

unnamed.png919×36 17.5 KB

I added this field in the class

public DateTime CreatedDate { get; set; }

while Indexing:

                    Title = Path.GetFileNameWithoutExtension(file),
                    CreatedDate = DateTime.Now

and

How can I retrieve last 5 records which are indexed recently based on the datetime field( Does sort work on this field? )

TIA

2

Hey @ASN, what version of NEST are you using and what version of Elasticsearch are you targeting?

By default, DateTime, DateTime?, DateTimeOffset and DateTimeOffset? are serialized using ISO 8601 format using the IsoDateTimeConverter in the Json.NET library. If you specify a different format to use in the mapping, then you will also need to change the serialization for the DateTime property to ensure that values get serialized according to the specified format; the format specified on the mapping only tells Elasticsearch about the format, the serializer used by the client also needs to be told.

You can specify the format to use when serializing DateTime by deriving from JsonNetSerializer and setting it as the serializer to use on ConnectionSettings. For example, you can add your own JsonConverter for handling DateTime

public class MyJsonNetSerializer : JsonNetSerializer
{
	public MyJsonNetSerializer(IConnectionSettingsValues settings) : base(settings) { }

	protected override IList<Func<Type, JsonConverter>> ContractConverters => new List<Func<Type, JsonConverter>>
	{
		t => {
			if (t == typeof(DateTime) ||
				t == typeof(DateTime?) ||
				t == typeof(DateTimeOffset) ||
				t == typeof(DateTimeOffset?))
			{
				return new  Newtonsoft.Json.Converters.IsoDateTimeConverter
				{
					DateTimeFormat = "yyyy-MM-dd HH:mm:ss"
				};
			}
				
			return null;
		}
	};
}

and

var pool = new SingleNodeConnectionPool(new Uri("http://localhost:9200"));
var connectionSettings = new ConnectionSettings(pool, settings => new MyJsonNetSerializer(settings));

IF you don't want to change the serialized format of all DateTime, then you can attribute the CreatedDate property on the Document class with your own JsonConverter type that specifies the format to use

public class MyDateTimeConverter : IsoDateTimeConverter
{
    public MyDateTimeConverter()
    {
        DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
    }
}

public class Document
{
   public int Id { get; set; }

   public string Title { get; set;}

   [JsonConverter(typeof(MyDateTimeConverter))]
   public DateTime CreatedDate { get; set;}
}

To answer your second question, you can also sort on this field to return the five most recently created documents

client.Search<Document>(s => s
     .Size(5)
         .Sort(so => so
             .Descending(d => d.CreatedDate)
        )
    )
);
3

Hi Russ,

Here are the details you have asked for:
elasticsearch 2.3.1
NEST 2.3.2
Elasticsearch.Net 2.3.2
Newtonsoft.Json {8.0.3}

TIA

4

Thanks! All of the above is relevant to that version of NEST.

You can find out more about defining your own json serializer in the Connecting section in the docs.

5

Thanks Russ.

6

Hi Russ,

I tried implementing this method and created an index. It is still throwing the same error.

while Indexing:

                    Title = Path.GetFileNameWithoutExtension(file),
                    CreatedDate = DateTime.Now

my mappings:

 var createIndexResponse =
                           client.CreateIndex(defaultIndex, c => c
                           .Mappings(m => m
                           .Map<Document>(mp => mp
                           .Properties(ps => ps
                               .String(s => s.Name(e => e.Title))
                               .Date(dt=>dt.Name(en=>en.CreatedDate))
                               . //other fields

Did I miss something in between?

7

What's the error? Can you include the .DebugInformation from the response? If you need more details to debug, you can also set .DisableDirectStreaming() on ConnectionSettings, which will also provide the request and response.

8

This is the response from the server.

### ES RESPONSE ###
{
  "took" : 31140,
  "errors" : true,
  "items" : [ {
    "create" : {
      "_index" : "tweaks",
      "_type" : "document",
      "_id" : "AVUk7yx1OnP5j8jagisq",
      "status" : 400,
      "error" : {
        "type" : "mapper_parsing_exception",
        "reason" : "failed to parse [createdDate]",
        "caused_by" : {
          "type" : "illegal_argument_exception",
          "reason" : "Invalid format: \"2016-06-06 16:58:21\" is malformed at \" 16:58:21\""
        }
      }
    }
  }

when it comes to this statement then it is throwing error.

var response = client.IndexMany(list, ConfigurationManager.AppSettings["index"]);
            if (!response.IsValid)
            {
                foreach (var item in response.ItemsWithErrors)
                    Console.WriteLine("Failed to index document {0}: {1}", item.Id, item.Error);
                Console.WriteLine(response.CallDetails.OriginalException.Message);
            }

This is the error from Client.IndexMany() call

Pasted image946×64 3.04 KB

9

Can you also provide the (json) mapping in Elasticsearch for the index?

10

11

The date format is not reflected in the mapping. If you're trying to update an existing mapping, your best bet is to create a new index with the new mapping and re-index your data into it. Looking at your C# mapping call

You also need to specifiy .Format(format) on .Date() i.e

var createIndexResponse =
                           client.CreateIndex(defaultIndex, c =>  c
                           .Mappings(m => m
                           .Map<Document>(mp => mp
                           .Properties(ps => ps
                               .String(s => s.Name(e => e.Title))
                               .Date(dt=> dt.Name(en=>en.CreatedDate).Format("yyyy-MM-dd HH:mm:ss"))
                               . //other fields
12

I do that if the client already exists

   if (!client.IndexExists(defaultIndex).Exists)
            {
                CreateIndex();
            }
            else
            {
                if (ConfigurationManager.AppSettings["syncMode"] == "Full")
                {
                    client.DeleteIndex(defaultIndex);
                    CreateIndex();
                }
            }
            return client;

but still the same mappings.. (just tried it now)

13

Here's a full working example to demonstrate

void Main()
{
     var pool = new SingleNodeConnectionPool(new Uri("http://localhost:9200"));
     var defaultIndex = "default-index";
     var connectionSettings = new ConnectionSettings(pool, settings => new MyJsonNetSerializer(settings))
               .DefaultIndex(defaultIndex)
               .PrettyJson()
               .DisableDirectStreaming()
               .OnRequestCompleted(response =>
                    {
                         // log out the request
                         if (response.RequestBodyInBytes != null)
                         {
                              Console.WriteLine(
                                   $"{response.HttpMethod} {response.Uri} \n" +
                                   $"{Encoding.UTF8.GetString(response.RequestBodyInBytes)}");
                         }
                         else
                         {
                              Console.WriteLine($"{response.HttpMethod} {response.Uri}");
                         }
                    
                    Console.WriteLine();

                         // log out the response
                         if (response.ResponseBodyInBytes != null)
                         {
                              Console.WriteLine($"Status: {response.HttpStatusCode}\n" +
                                         $"{Encoding.UTF8.GetString(response.ResponseBodyInBytes)}\n" +
                                         $"{new string('-', 30)}\n");
                         }
                         else
                         {
                              Console.WriteLine($"Status: {response.HttpStatusCode}\n" +
                                         $"{new string('-', 30)}\n");
                         }
                    });
                    
     var client = new ElasticClient(connectionSettings);
    
    if (client.IndexExists(defaultIndex).Exists)
        client.DeleteIndex(defaultIndex);

     var createIndexResponse = client.CreateIndex(defaultIndex, c => c
          .Mappings(m => m
               .Map<Document>(mp => mp
                    .Properties(ps => ps
                            .String(s => s
                              .Name(e => e.Title)
                         )
                            .Date(dt => dt
                              .Name(en => en.CreatedDate)
                              .Format("yyyy-MM-dd HH:mm:ss")
                         )
                    )
               )
          )
     );

     client.Index(new Document
     {
          Id = 1,
          Title = "Example document",
          CreatedDate = DateTime.Now
     });
     
    client.Refresh(defaultIndex);
    
    client.GetMapping<Document>();
    
     client.Search<Document>(s => s
          .Size(5)
          .Sort(so => so
               .Descending(d => d.CreatedDate)
          )
     );
}

public class MyJsonNetSerializer : JsonNetSerializer
{
     public MyJsonNetSerializer(IConnectionSettingsValues settings) : base(settings) { }

     protected override IList<Func<Type, JsonConverter>> ContractConverters => new List<Func<Type, JsonConverter>>
     {
          t => {
               if (t == typeof(DateTime) ||
                    t == typeof(DateTime?) ||
                    t == typeof(DateTimeOffset) ||
                    t == typeof(DateTimeOffset?))
               {
                    return new  Newtonsoft.Json.Converters.IsoDateTimeConverter
                    {
                         DateTimeFormat = "yyyy-MM-dd HH:mm:ss"
                    };
               }
                    
               return null;
          }
     };
}

public class Document
{
     public int Id { get; set; }

     public string Title { get; set;}

     public DateTime CreatedDate { get; set;}
}

...

1 Like
14

If you run this (take a look at LINQPad), you get the following

HEAD http://localhost:9200/default-index?pretty=true

Status: 200

------------------------------

DELETE http://localhost:9200/default-index?pretty=true

Status: 200
{
  "acknowledged" : true
}

------------------------------

PUT http://localhost:9200/default-index?pretty=true 
{
  "mappings": {
    "document": {
      "properties": {
        "title": {
          "type": "string"
        },
        "createdDate": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss"
        }
      }
    }
  }
}

Status: 200
{
  "acknowledged" : true
}

------------------------------

PUT http://localhost:9200/default-index/document/1?pretty=true 
{
  "id": 1,
  "title": "Example document",
  "createdDate": "2016-06-06 19:41:05"
}

Status: 201
{
  "_index" : "default-index",
  "_type" : "document",
  "_id" : "1",
  "_version" : 1,
  "_shards" : {
    "total" : 2,
    "successful" : 1,
    "failed" : 0
  },
  "created" : true
}

------------------------------

POST http://localhost:9200/default-index/_refresh?pretty=true

Status: 200
{
  "_shards" : {
    "total" : 10,
    "successful" : 5,
    "failed" : 0
  }
}

------------------------------

GET http://localhost:9200/default-index/_mapping/document?pretty=true

Status: 200
{
  "default-index" : {
    "mappings" : {
      "document" : {
        "properties" : {
          "createdDate" : {
            "type" : "date",
            "format" : "yyyy-MM-dd HH:mm:ss"
          },
          "id" : {
            "type" : "long"
          },
          "title" : {
            "type" : "string"
          }
        }
      }
    }
  }
}

------------------------------

POST http://localhost:9200/default-index/document/_search?pretty=true 
{
  "size": 5,
  "sort": [
    {
      "createdDate": {
        "order": "desc"
      }
    }
  ]
}

Status: 200
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : null,
    "hits" : [ {
      "_index" : "default-index",
      "_type" : "document",
      "_id" : "1",
      "_score" : null,
      "_source" : {
        "id" : 1,
        "title" : "Example document",
        "createdDate" : "2016-06-06 19:41:05"
      },
      "sort" : [ 1465242065000 ]
    } ]
  }
}

------------------------------

Based on this, I think the issue must be somewhere in your code. If ConfigurationManager.AppSettings["syncMode"] is not Full for example, your code won't delete the existing index.

15

Yes. It should use the existing index if the syncMode is "Full" and in App.Config it is set to "Full".

I tried to implement the same . Now the mapping style has been changed. But still getting the same error.

16

What does the request json look like that you are sending? Can you include it in your reply (as text)?

Have you applied the serialization change as well, either as an attribute on the type property, or by creating your own derived json serializer?

17

Ok I got it.

I changed everything except this statement.
var connectionSettings = new ConnectionSettings(pool, settings => new MyJsonNetSerializer(settings))

Sorry for my mistake. Because of it you had to write a working code. Sorry again.
And Thanks a ton Russ..

But how is this different from the other approach you have mentioned? (like the below one)
Even in this approach should I add the statement which I missed like the above one?

IF you don't want to change the serialized format of all DateTime, then you can attribute the CreatedDate property on the Document class with your own JsonConverter type that specifies the format to use

 public class MyDateTimeConverter : IsoDateTimeConverter
 {
     public MyDateTimeConverter()
     {
         DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
     }
 }

 public class Document
 {
    public int Id { get; set; }
    public string Title { get; set;}
    [JsonConverter(typeof(MyDateTimeConverter))]
    public DateTime CreatedDate { get; set;}
 }
18

changing the date format on settings using a derived serializer like MyJsonNetSerializer will apply the serialization format to all DateTime - you may not want to do this, in which case, applying a custom converter on the type property will apply the serialization format only to that property.

Up to you which one you choose :slight_smile:

19

Thanks Russ. I understood now.
If I'm using a custom converter, even then should I add the converter to the settings to reflect??

20

If you're using a custom converter in an attribute applied to a property, you don't need to provide your own derived json serializer to settings. This is all Json.NET specific, so it's worth having a look at its documentation

1 Like