Intersection of two lists stored as string

Elastic Stack Elasticsearch
1

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 .

2

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