Hello Community!
I would like to create a scripted field that checks two properties of a nested field.
I have an ES index that contains information about processes. A process is configured as a nested object. Each doc can have more than one process. It is also possible to have more than one process of the same kind (same processDefinitionKey).
This is how the processes are structured at the moment:
"process" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"created" : {
"type" : "date",
"format" : "date_optional_time"
},
"ended" : {
"type" : "date",
"format" : "date_optional_time"
},
"id" : {
"type" : "text",
"index" : false
},
"processDefinitionKey" : {
"type" : "keyword"
},
"processDefinitionName" : {
"type" : "text",
"fields" : {
"raw" : {
"type" : "keyword",
"index" : false
}
},
"analyzer" : "case_insensitive_sort"
}
}
}
Now I am interested in all active processes of a specific kind. So they must have a specific processDefinitionKey and must not have an ended property yet. I've written an ES query for it:
{
"query": {
"nested": {
"path": "process",
"query": {
"bool": {
"must": {
"match": {
"process.processDefinitionKey": "service_agreement"
}
},
"must_not": {
"exists": {
"field": "process.ended"
}
}
}
}
}
}
}
I have added this query as a filter that I can use on the Kibana dashboards. I think it works as it should.
However, I would prefer to add this property "has an active process of this kind" as a column in a search table by creating a scripted field for it. This is the scripted field I have come up with so far (in Painless):
doc['process.processDefinitionKey'].contains('service_agreement') && doc['process.ended'].empty
But the results here do not align with the query above. I am afraid that this script checks whether there is a process with the correct processDefinitionKey and a process without the ended property. But it does not check whether both properties are true for the same process.
How can I make sure the scripted field checks both the processDefinitionKey and the ended property of the same process?
I use Kibana 6.4.2
EDIT:
Example of a document where the scripted field should return true (the service_agreement process has not ended yet, the other process has):
{
"_index": ...,
"_type"...,
"_id": ...,
"_version": ...,
"_score": ...,
"_source": {
"type": ...,
...,
"process": [
{
"id": "130707",
"created": "2017-09-11T09:50:52.000+02:00",
"ended": "2017-09-13T10:16:43.000+02:00",
"processDefinitionKey": "so_ondertekenproces",
"processDefinitionName": "Samenwerkingsovereenkomst",
"incidents": []
},
{
"id": "c2a83c07-15f7-11e7-a20e-0242ac120004",
"created": "2017-03-31T11:52:32.000+02:00",
"processDefinitionKey": "service_agreement",
"processDefinitionName": "Service Agreement",
"incidents": []
}
]
},
"fields": {
"process.created": [
"2017-03-31T09:52:32.000Z",
"2017-09-11T07:50:52.000Z"
],
"process.ended": [
"2017-09-13T08:16:43.000Z"
]
}
}
Example of a document where the scripted field should return false:
{
"_index": ...,
"_type": ...,
"_id": ...,
"_score": ...,
"_source": {
"type": ...,
...,
"process": [
{
"id": "17154",
"created": "2017-05-24T13:21:40.000+02:00",
"ended": "2017-05-24T13:23:24.000+02:00",
"processDefinitionKey": "so_ondertekenproces",
"processDefinitionName": "Samenwerkingsovereenkomst",
"incidents": []
},
{
"id": "17263",
"created": "2017-05-24T13:23:29.000+02:00",
"processDefinitionKey": "so_ondertekenproces",
"processDefinitionName": "Samenwerkingsovereenkomst",
"incidents": []
},
{
"id": "f1cd056e-15f7-11e7-a20e-0242ac120004",
"created": "2017-03-31T11:53:51.000+02:00",
"ended": "2017-10-10T11:35:47.000+02:00",
"processDefinitionKey": "service_agreement",
"processDefinitionName": "Service Agreement",
"incidents": []
}
]
},
"fields": {
"process.created": [
"2017-03-31T09:53:51.000Z",
"2017-05-24T11:21:40.000Z",
"2017-05-24T11:23:29.000Z"
],
"process.ended": [
"2017-05-24T11:23:24.000Z",
"2017-10-10T09:35:47.000Z"
]
}
}