Logstash -> array field -> subquery

[seboraid (Sebastian Perez)]

Hi!

Im looking to add data from a subquery (i dont jnow if this is possible) to an array field in a elastic index.

I have the following tables in MySQL: transactions and applied_rules
where applied_rules has the following fields transaction_id and rule_name

transactions has a 1 to many relation with applied_rules and I want to put all this applied_rules records in an array in transactions index in elastic.

I think this is a very common case, but i dont find how to do this in the docs.

Thanks for your help

[Badger]

If you have a transaction_id (possibly from using a jdbc input on the transactions table) then you could use a jdbc_streaming or jdbc_static filter to do the lookup in the applied_rules table. Those filters return an array of hashes (it has to be an array because the result set may have multiple rows, and they have to be hashes because you can fetch multiple columns).

If you are only fetching one column and want the array to contain strings rather than hashes with a single key/value pair you could use a ruby filter to do that. I have not tested it, but something like

ruby {
    code => '
        a = event.get("someField")
        if a.is_a? Array
            newA = []
            a.each { |x|
                newA << x["rule_name"]
            }
            event.set("someField", newA)
        end
    '
}