Do I need a nested query or foreach or a create a new index?

[clearbluelou (Michael Friddell)]

I'm new to Elasticsearch and Kibana. I haven't really gotten to a point of understanding of the JSON-like syntax. I've just been using the filters and some Lucene queries. Please be gentle. This is hard to explain.

I'm dealing with voice call records that are indexed by a call identifier. The fields I'm interested in are named 'messageKey' and 'message'. I need to find all call IDs with (messageKey : ReportKey) AND (message : [9837 TO 9839]) and then find the record with (message : waitDurationInQueueUntilAccepted) for each of those call IDs.

I read about nested and foreach queries but I'm out of my depth. I know this is probably not enough information to answer my question. I'm not sure how best to ask. I hope someone will be patient enough to help me talk through it. I'm open to consider that I'm asking the wrong question entirely.

[majagrubic (Maja)]

You definitely don't need a new index. In Kibana Discover, you can type a query with what you have just described:
messageKey : ReportKey AND message >= 9837 AND message <= 9839

and then add a filter for the additional message parameter.

Here's an example of how it might look like for sample data:

[clearbluelou (Michael Friddell)]

Maja,

Thank you very much for you kind reply. Unfortunately, doing as you suggested causes no results to be found. My conclusion is that since the query causes the results to only include records in which message is between 9837 and 9839, filtering those results for records in which message = waitDurationInQueueUntilAccepted is meaningless. The problem, as I see it, is that both the 9837-9839 and the waitDurationInQueueUntilAccepted records are in the same 'message' field.

[Tomo_M (Tomohiro Mitani)]

So you have data structure something like "entity attribute value" table of SQL?

{"callID":"001", "messageKey": "ReportKey", "message":9838}
{"callID":"001", "messageKey": "ReportEvent", "message":waitDurationInQueueUntilAccepted}
{"callID":"002", "messageKey": "ReportKey", "message":9901}
{"callID":"002", "messageKey": "ReportEvent", "message":waitDurationInQueueUntilAccepted}

To query what you need, you need JOIN function, though Elasticsearch doesn't support it. It is because of performance issue to implement JOIN in highly distributed system.

I truly recommend to have flattened data structure as one document per one callID, such that:

{"callID":"001", "ReportKey":9838, "ReportEvent": "waitDurationInQueueUntilAccepted"}
{"callID":"002", "ReportKey":9901, "ReportEvent": "waitDurationInQueueUntilAccepted"}

[clearbluelou (Michael Friddell)]

Tomo,

Thank you for your reply. Yes, you have identified the basic structure of our data. However, we also have thousands, if not tens of thousands, of other data in the "message" field. As such, it is not possible to flatten it. We have an extremely complex environment.

[Tomo_M (Tomohiro Mitani)]

I see. Then you may use terms aggregation on call ID and bucket selector aggregation to select call IIDs meeting the condition what you want.

If there are performance issue or exeeding max bucket limitation, using transform function could a solution.