# Array search from Logstash elasticsearch plugin

**URL:** <https://discuss.elastic.co/t/array-search-from-logstash-elasticsearch-plugin/355258>\
**Category:** Logstash\
**Created:** [March 12, 2024, 7:27pm UTC](https://discuss.elastic.co/t/array-search-from-logstash-elasticsearch-plugin/355258 "2024-03-12T19:27:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![RRGTHWAR1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rrgthwar1/32/130084_2.png) [@RRGTHWAR1](https://discuss.elastic.co/u/RRGTHWAR1)\
**Post date:** [March 12, 2024, 7:27pm UTC](https://discuss.elastic.co/t/array-search-from-logstash-elasticsearch-plugin/355258/1 "2024-03-12T19:27:42Z")

</div>

I'm bringing records from a SQL Server database into Elasticsearch using Logstash, and I need to convert a numerical field that could apply several times to each SQL Server record into one array field in the Elasticsearch record. Each of the records that become ES documents is associated with one or more other records (I'll call those obj\_1), which in turn are associated with the tag fields. Something like this:

SQL Server rows:

```auto
id obj_1 obj_1_tag_field
1 A 1
1 B 2
1 B 3
2 A 1
2 C 4

```

Desired ES documents:

```auto
{
    "id": 1,
    "obj_1": [
        "A",
        "B"
    ],
    "tag_field": [
        1,
        2,
        3
    ]
},
{
    "id": 2,
    "obj_1": [
        "A",
        "C"
    ],
    "tag_field": [
        1,
        4
    ]
}

```

Ordinarily, I'd do a join in SQL Server and use a Logstash aggregate filter to combine the SQL Server rows. But in this case, I'm already aggregating some other fields, and because of the nesting and aggregating, the number of rows for each id tends to explode, so pulling 10,000 ids creates something like 4 million rows, which quickly overwhelms SQL.

However, obj\_1 already exists in ES with its associated tags and rarely changes. A, B and C above would look something like this:

```auto
{
    "id": "A",
    "tag_field": [
        1
    ]
},
{
    "id": "B",
    "tag_field": [
        2,
        3
    ]
},
{
    "id": "C",
    "tag_field": [
        4
    ]
}

```

So I'd like to use the obj\_1 links to query ES with the logstash-elasticsearch-filter plugin and use its tag\_field array to update the Logstash event record. The only issue here is that the obj\_1 field for the logstash event is an array. I know how to use the plugin to query for a single Elasticsearch document using an id or something, but I've had trouble doing it with an array.

With all that said, here's my main question: is there a way to use the logstash-elasticsearch-filter plugin to query by matching an array from the event to ids in Elasticsearch? Or can it be done with a Ruby filter and a loop somehow?

---

<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:** [April 9, 2024, 7:28pm UTC](https://discuss.elastic.co/t/array-search-from-logstash-elasticsearch-plugin/355258/2 "2024-04-09T19:28:41Z")

</div>

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