Get documents with dynamic max field

Elastic Stack Elasticsearch
1

Hello there!

First of all, thanks to the team for the work, and sorry for the title, I don't know how to summarize my issue.

I need help for something looking very simple for a human but I can't find how to do with ES:

Let's imagine I have these simplified documents in ES:
|id |status |version_number|created_at |
|XXX1 |review |4 |2020-02-05 11:22:21|
|XXX2 |accepted |5 |2020-02-05 11:23:08|
|XXX3 |published|10 |2020-02-05 11:23:24|
|XXX4 |review |14 |2020-02-05 13:58:10|
|XXX5 |accepted |15 |2020-02-05 13:58:16|
|XXX6 |published|20 |2020-02-05 13:58:22|
|XXX7 |review |23 |2020-02-05 14:32:34|
|XXX8 |accepted |24 |2020-02-05 14:32:42|
|XXX9 |review |28 |2020-02-05 14:33:28|
|XXX10|accepted |29 |2020-02-05 14:33:33|
|XXX11|published|30 |2020-02-05 14:33:37|

I need to get all documents, minus XXX7 and XXX8, because they aren't the last one before the next published one. So the query must get documents completing these conditions:

*status must be "review", "accepted" or "published"
*get documents with "review" or "accepted" status with the higher version number before a published one.

So hits should be:
|id |status |version_number|created_at |
|XXX1 |review |4 |2020-02-05 11:22:21|
|XXX2 |accepted |5 |2020-02-05 11:23:08|
|XXX3 |published|10 |2020-02-05 11:23:24|
|XXX4 |review |14 |2020-02-05 13:58:10|
|XXX5 |accepted |15 |2020-02-05 13:58:16|
|XXX6 |published|20 |2020-02-05 13:58:22|
|XXX9 |review |28 |2020-02-05 14:33:28|
|XXX10|accepted |29 |2020-02-05 14:33:33|
|XXX11|published|30 |2020-02-05 14:33:37|

Thanks a lot for your help!

Olivier

2

I added a field to "invalidate" those I don't need.

Issue closed

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.