Advice for mapping one to many relationships from ER DB

Elastic Stack Logstash
2

Look into using string aggregations in SQL.
Postgres: https://stackoverflow.com/a/43944/5349531
Oracle: https://stackoverflow.com/a/12421460/5349531
Mysql: https://stackoverflow.com/a/276949/5349531

There are two ways to do this.

  1. Put all the complexity in the JDBC Input statement by adding the aggregation into the full query statement.
  2. Make the JDBC Input query statement a simple one that just gets new tickets since the last run and use the new JDBC Streaming Filter to fetch and add the aggregated string from the RootCauses table.

NOTE: in either solution the case of the field names might be forced into lowercase.

Solution 1

  • Use the JDBC Input to build an Event from the JOINed Tickets and RootCauses tables adding the string aggregation in as an AS named field. Obviously you can build this statement using a SQL GUI tool.
  • Use the split function from the Mutate Filter to split to an array if needed.

Solution 2

  • Start with the JDBC Input to build an Event from the Tickets table.
  • Use the JDBC Streaming filter to fetch the aggregated string from the RootCauses table using the TicketID from each event. You will probably not benefit from results caching because each ticket is unique I guess.
  • Use the split function from the Mutate Filter to split to an array if needed.

Partial config for JDBC Streaming Filter.

  statement => "SELECT string_agg(Causes, ',') AS agg_causes FROM RootCauses WHERE PersonId = :ticketid GROUP BY PersonId;"
  parameters => { "ticketid" => "ticket_id"}

NOTE: the two strings in the parameters map are: LHS is the "label" used in the SQL statement - in the above example its :ticketid and the RHS is the event field to pluck the substituted value from.

General:
Note: that complex statements will slow things down. Having two JDBC calls with simple statements may be faster than one complex call.