DateRange in Elasticsearch .net on fields

Question

I am trying to achieve something like

.Where(m => m.PublishedDate.Value < m.LastUpdatedDate)

in Elastic search. Here PublishedDate is nullable.

So far I have tried this the following but it doesn't seem to be working.

var res2 = client.Search<Campaign>(c => c
    .Source(s => s.Includes(i => i
        .Field(f => f.CampaignId)
        .Field(f => f.Offers.First().DateLastUpdated)
        .Field(f => f.Offers.First().DateLastPublished)))
    .Query(q => q
        .Bool(b => b
            .Must(m => m.Exists(t => t.Field(f => f.Offers.First().DateLastPublished)))
            .Must(m => m.Term(t => t.Offers.First().DateLastPublished.Value < t.Offers.First().DateLastUpdated, true))
            .Should(s =>
                s.Term(t => t.Offers.First().Approved, true), s =>
                s.Term(t => t.Offers.First().Deleted, true))
            .MinimumShouldMatch(new MinimumShouldMatch(1))))
    .Size(1000)
);

Can somebody please help? Thanks

Answer 1

I was able to achieve it using following way.

var res3 = client.Search<Campaign>(c => c
    .Source(s => s.Includes(i => i
        .Field(f => f.CampaignId)))
    .Query(q => q.ConstantScore(cs => cs.Filter(fs => fs
        .Bool(b => b
            .Must(m =>
                m.Exists(e => e.Field(f => f.Offers.First().DateLastPublished)), m =>
                m.Exists(e => e.Field(f => f.Offers.First().DateLastUpdated)), m =>
                m.Script(s => s.Script(ss => ss
                    .Source("doc['offers.dateLastPublished'].value.getMillis() < doc['offers.dateLastUpdated'].value.getMillis()"))))
            .Should(s =>
                s.Term(t => t.Offers.First().Approved, true), s =>
                s.Term(t => t.Offers.First().Deleted, true))
            .MinimumShouldMatch(new MinimumShouldMatch(1))))))
    .Size(1000))