We have an index called "queues" that stores snapshots of queue states as follows:
Each document contains (along some other fields) the following fields:
timestamp: type=date, and it is searchable, aggregatable
items: type=string, and it is searchable
The field items contains a large string consisting thousands of unsorted IDs, for example
items="2070213614 2909589973 2771050342 ..."
I want to query two rows (for two given timestamps) and calculate the intersection (cut set) of both item lists. Ultimately we need to know the size of this cut set.
How do we deal with this problem, if we want to display the calculated size of this intersection in an infographics canvas in Kibana?
I would be very grateful for a SQL statement that might be used by a Kibana canvas.
Here is a screenshot of an exemplary data set: example|690x80 .