Effective date Filter

Elastic Stack Elasticsearch
1

Hi,

I've got an index, indexed from a mssql server database using nest, so far so good. Most properties are effective dated, i.e. when a property "changed", what will happen is that created a new record with a timestamp.

In sql, when we want all records where the effective date for some property that equals false and the is 2014-10-09, I'll do following:

select * from MainData inner join EffectiveData on MainData.Id = EffectiveData.MainDataId 
Having MAX(EffectiveData.Date) <= '2014-10-09' and EffectiveData.Property = 0

Now I'm trying to do the same in Elastic and I don't know how. I asked the same question in StackOverflow, but I can't put the code woking, mainly because I don't understand what the code does:

Can anyone helo me?

2

To make it easier to understand can you start with the business question rather than the technicalities of SQL/Json?

Those records look like start+end ranges for users - let's for want of a better word call them "sessions".

Do you want to find:

  • Still-active sessions?
  • Recently expired sessions?
  • Something else?
3

So for the business question, let divide by parts: I have a list of employees of a company. Each employee has the normal values (not dated) like, Id, Name, Birth date.
Now, for each employee, I've got the data relative to company, that is dated. For this case, lets have a bool field that says if the employee is Director or not, and this can change over time, like a promotion. So an example data would be

Id: 1,
Name : Alberto Soares,
BirthDate : 1990-01-01,
isDirector {
   items:
   [
       {
             effectiveDate : 2010-05-16,
             propertyValue : false // I'm a simple employee
       },
       {
             effectiveDate : 2011-09-22,
             propertyValue : true // Because I've worked very hard, I'm now a director
       }
   ]
}

So now I've this data (some more records) and I want to know, at any given date, who were the directors of the company, lets say 2010-12-20. In sql I do it like the OP, but I'm complety lost on how to do it Elastic.

I hope that my example makes sense...

Edit: On my mappings (I'm using nest) I've already nested the Items (effectiveDate with propertyValue), was this a correct thing to do?

Edit2: My problem right (for the searches that I made) is this: I want a list of all current employees that are not directors. When I do a search where propertyValue : false, and the effectiveDate < today, the record with id 1 is returned, because it has a record that matches the criteria. So the main ideia here is to get only the current is Director record, with sql I would use max(effectiveDate) to get this, but I'm completly lost on ES.

4

Can anyone help me?

Edit: Solved @ stackoverflow