Where / inner join capability in SEQ sql

[samuelfiery2]

Hi, I have some logs that i need to aggregate, things is one type of the logs only have part of the values of the other logs.

Are there any way for seq sql to reference properties like"table_name.column_name" similiar to SQL?
For example :
inner join :
(EventType = 0x00001) on ((EventType = 0x00002.Data.publisherOrderId) = (EventType = 0x00001.Data.publisherOrderId))

or maybe :
where EventType.0x00001.data.publisherOrderId = EventType.0x00002.data.publisherOrderId

Here is the first type of the logs :
Message : Response from 3rd party - Order12345
Data.publisherOrderId : Order12345
Data.responseHtml :{"user":"someuser01","request_id":" Order12345"}

Second type of the logs :
Message : [Check Transaction] Order12345 - transaction failed
Data.errorMessage : failed {error message}, is absent on success
Data.publisherOrderId : Order12345

Are there any way to aggregate those logs to something like this, :

User                trans_count       trans_failed        
someuser01          10                     3                      
someuser02          5                     null                      
someuser03          3                       1                         

using :
count(Data.publisherOrderId as trans_count)
Group by fromJson(.Data.responseHtml).user, Data.errorMessage)

Best regards.

[liammclennan]

Hi @samuelfiery2

I think what you want requires joins or sub-queries which Seq does not support. The only options I can think of are:

1. Add the user to the transaction failed event
2. Do the join in the client that requests data from Seq

[samuelfiery2]

Thanks for the answer liam, that clears things up.
Saw the seq sql docs make no mention of joins/sub queries,i was expecting theres may be a workaround using the seq sql though.
The first options is pretty much straightforward, ill go and bug the devs to add more properties to the logs 😈

[liammclennan]

That is often the simplest solution. Good luck.