SiddhiQL complex filter condition on table

Question

I have a table T(name string,id string) and i receive events from a stream X(name string,id string). When i receive an event, if the name does not exist in table T, I want to insert the name and id into table and send that event to output stream. If the name exists in the table, i want to send that event to output stream only if the id against the name in the table matches to that of the id received in event. Ex -

Table Data
name     |id
qwerty   |12345

Event 1 = {qwerty,123}
Event 2 = {qwerty,12345}
Event 3 = {asdf,12}

In the above scenario, I want to ignore event 1 , send event2 to output stream and add event3 name and id in table and then send it to output stream.

Is this possible in siddhiql?

Answer 1

You can use siddhi table joins especially left outer join with name as the condition and filter out other cases along the way

1. Inner joins for case 1 and 2, This will only return event if the name and id match.

from XStream left outer join ATable 
  on XStream.name == ATable.name 
select Xstream.name, XStream.id, ATable.name as tableName, ATable.id as tableId 
insert into OutputStream;

from Outputstream[tableName is null] 
select name, id 
insert into ATable;

-- Here if the table name is not null it will be equal to XStream.name based on the first query
from Outputstream[not(tableName is null) and (tableId == id)] 
select name, id 
insert into FilteredStream;