# Logstash -\> array field -\> subquery

**URL:** <https://discuss.elastic.co/t/logstash-array-field-subquery/276584>\
**Category:** Logstash\
**Created:** [June 21, 2021, 10:29pm UTC](https://discuss.elastic.co/t/logstash-array-field-subquery/276584 "2021-06-21T22:29:59Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![seboraid](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seboraid/32/90619_2.png) [@seboraid](https://discuss.elastic.co/u/seboraid)\
**Post date:** [June 21, 2021, 10:29pm UTC](https://discuss.elastic.co/t/logstash-array-field-subquery/276584/1 "2021-06-21T22:29:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [June 22, 2021, 12:06am UTC](https://discuss.elastic.co/t/logstash-array-field-subquery/276584/2 "2021-06-22T00:06:42Z")

</div>

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
    '
}
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 20, 2021, 12:07am UTC](https://discuss.elastic.co/t/logstash-array-field-subquery/276584/3 "2021-07-20T00:07:21Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.