Search code examples
sqlarrayscomplex-event-processingesper

Esper - Array Representation of query results

Let's assume that I have the CallPOPDWH event. The event has 4 columns. A type column, a name column, a detection timestamp column and an attributes column. Esper produces the following 3 tuples:

[Event, CallPOPDWH, 1456938481000, {call_start_date=7355448481000, calling_number=+3859121JMOYL, call_direction=I}]  
[Event, CallPOPDWH, 1456938481000, {call_start_date=5464538486000, calling_number=+3859121JMOYL, call_direction=I}]
[Event, CallPOPDWH, 1456938481000, {call_start_date=1456345351000, calling_number=+3859121JMOYL, call_direction=I}]

I want to execute a query which will collect all the different call_start_date values for which the calling number and detection timestamp is the same.

For example: 

[Event, NewEvent, 1456938481000, {call_start_date=[7355448481000, 5464538486000, 1456345351000], calling_number=+3859121JMOYL, call_direction=I}]

I have read Esper's documentation and although it says that you can create a schema to represent the newEvent where you can declare a property as an array,i haven't found a working example.

I don't know what kind of a query do i need.Do i need a pattern query or a select query with subqueries for specific attributes?Any suggestion will be appreciated.

Solution 
  • select window(call_start_date).distinctOf() from CallPOPDWH#keepall group by calling_number, detectionTimestamp

    a few notes

    • the #keepall for remembering all values could be time window or other
    • add "output last every X seconds" for example when continuous output is not wanted