Elasticsearch .net conditional sort

[Orhun_Karapinar]

I am trying to write an elasticsearch query which gets most recent top 100 from orders and sales, but my problem is orders have shipment_date. I want to use "shipment_date" for orders, "created_date" for sales to sort them. I couldn't find a way to do this, there are some solutions with script but they didn't work for me maybe because I am using elasticsearch version 6. Thank you

var response = client.Search<DataModel>(x => 
x.Index("order*,sale*").Type("")
.Query(q =>
q.Term(t => t.customerID, customerID) &&
q.DateRange(d => d
    .Field(f => f.created_date)
    .GreaterThanOrEquals(fromDate)
    .LessThanOrEquals(toDate)
    )
)
.From(0)
.Size(100)
.Sort(s => s.Field(f => f
.Field("created_date")
.Order(SortOrder.Descending)
))
);

[forloop (Russ Cam)]

if only orders contain a "shipment_date" and only sales contain a "created_date", the following should work using a scripted sort:

var response = client.Search<DataModel>(x => x
	.Index("order*,sale*")
	.AllTypes()
    .Query(q =>
		q.Term(t => t.customerID, customerID) &&
		q.DateRange(d => d
		   .Field(f => f.created_date)
		   .GreaterThanOrEquals(fromDate)
		   .LessThanOrEquals(toDate)
		   )
		)
		.From(0)
		.Size(100)
		.Sort(s => s
			.Script(ss => ss
				.Script(sd => sd
					.Inline("doc['shipment_date'] ? doc['shipment_date'].value : doc['created_date'].value")
					.Lang("painless")
				)
			)
		)
	)
);

If you need to sort like this frequently, it may make sense to index the "shipment_date" for orders and "created_date" for sales into a commonly named field e.g. "sort_date", using copy_to, then sort on this field. It would perform better than a scripted sort.

[Orhun_Karapinar]

Thanks for the response, looks like idea of using a script works. Thank you again, just for some reason the script you provided didn't give me any results, so I used that and worked:

 if (doc.containsKey('shipment_date')) { doc['shipment_date'].value } else { doc['created_date'].value}

Also, I realized that my elastic search version is 5.5, one more small question can I use the same trick for date range as well?

[forloop (Russ Cam)]

Glad you got it working

@Orhun_Karapinar

can I use the same trick for date range as well?

Not for a range query, no. You could write a script query to do this but similarly to script sorting, the performance of the query would not be as good as using the range query, but the performance might be acceptable for your use case. You can write a compound query however that combines

• a range query on shipment_date with a term query on the sale* _index (both as filter clauses in a bool query)

with

• a range query on created_date with a term query on the order* _index (both as filter clauses in a bool query)

both as should clauses in a bool query. Here's something similar as an example.

[Orhun_Karapinar]

Thanks for the response again. I have tried to use script query for date but I think maybe I am missing something with casting, I tried similar things like that:

var response = client.Search<DataModel>(x => x
.Index("order*,sale*")
.AllTypes()
.Query(q =>
	q.Term(t => t.customerID, customerID) &&
	q.Script(sc => sc
		.Inline("if (doc.containsKey('shipment_date')) { doc['shipment_date'].value < 2018-02-20} else { doc['created_date'].value < 2018-02-20}"))
	)
	.From(0)
	.Size(100)
	.Sort(s => s
		.Script(ss => ss
			.Script(sd => sd
				.Inline("if (doc.containsKey('shipment_date')) { doc['shipment_date'].value } else { doc['created_date'].value}")
				.Lang("painless")
			)
		)
	)
)
);

Also I am sorry; the second solution you recommended looks little bit complicated for me, do you have something more simple or can you explain me little bit more. Thank you so much again!