# Logstash Pipeline for Nested data from sql

**URL:** <https://discuss.elastic.co/t/logstash-pipeline-for-nested-data-from-sql/305688>\
**Category:** Logstash\
**Created:** [May 26, 2022, 8:57am UTC](https://discuss.elastic.co/t/logstash-pipeline-for-nested-data-from-sql/305688 "2022-05-26T08:57:29Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vikram\_singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vikram_singh/32/66490_2.png) [@vikram\_singh](https://discuss.elastic.co/u/vikram_singh)\
**Post date:** [May 26, 2022, 8:57am UTC](https://discuss.elastic.co/t/logstash-pipeline-for-nested-data-from-sql/305688/1 "2022-05-26T08:57:29Z")

</div>

Hi,  
I am trying to insert nested data from sqldb to Elasticsearch index.

I create a query with joins and insert data into Elasticsearch index.  
I used aggregate, but it is not giving correct result.

Mapping

```auto
PUT testing1111
{
    "mappings" : {
      "properties" : {
        "my_task_id" : {
          "type" : "long"
        },
       "my_details" : {
          "type" : "text"
        },
		"posts" : {
          "type" : "nested",
		  "properties": {
          "s_id": {
            "type": "long"
          },
		  "status": {
            "type": "text"
          }
        }}
      }
   }
}

```

aggregate Filter

```auto
filter {
    aggregate {
        task_id => "%{my_task_id}"
        code => "
            map['my_task_id'] = event.get(my_task_id)
			map['my_details'] = event.get(my_details)
			map['posts'] ||= []
            map['posts'] << {

                'status' => event.get('status'),
                's_id' => event.get(s_id)
            }
        event.cancel()"
        push_previous_map_as_event => true
        timeout => 80
    }
}

```

Data is

![elasticsearch_nested_error](https://us1.discourse-cdn.com/elastic/original/3X/8/2/82ff9174b67a38bac2dbe83e93bc39d3cd80f887.png)

Target to insert data into this format into index

```auto
{ "my_task_id": 2,
  "my_details" : "solve problem two",
  "posts" : [
  {"status" : 1,
  "s_id" : 3
  },
  {"status" : 1,
  "s_id" : 7
  },
  {"status" : 2,
  "s_id" : 99
  },
  {"status" : 2,
  "s_id" : 43
  }
  ]
}

```

Data coming form sql. I make a jdbc connection, my\_task\_id and my\_details field coming from table A and status, s\_id coming from table B

I also used mutate filter but did not achieve targeted result.

Thanks

---

<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:** [May 26, 2022, 4:21pm UTC](https://discuss.elastic.co/t/logstash-pipeline-for-nested-data-from-sql/305688/2 "2022-05-26T16:21:03Z")

</div>

> [@vikram\_singh](#):
>
> I used aggregate, but it is not giving correct result.

What result does it produce, and what do you not like about it?

---

<div class="post-metadata">

**Author:** ![vikram\_singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vikram_singh/32/66490_2.png) [@vikram\_singh](https://discuss.elastic.co/u/vikram_singh)\
**Post date:** [May 27, 2022, 4:57am UTC](https://discuss.elastic.co/t/logstash-pipeline-for-nested-data-from-sql/305688/3 "2022-05-27T04:57:05Z")

</div>

> [@vikram\_singh](#):
>
> ```auto
> map['my_task_id'] = event.get(my_task_id)
> map['my_details'] = event.get(my_details)
> map['posts'] ||= []
> map['posts'] << {
> 
> 'status' => event.get('status'),
> 's_id' => event.get(s_id)
> 
> ```

Hi,  
I find the error.  
I miss single quotes on my\_task\_id ,my\_details and s\_id.

Correct filter is Here

```auto
filter {
    aggregate {
        task_id => "%{my_task_id}"
        code => "
            map['my_task_id'] = event.get('my_task_id')
			map['my_details'] = event.get('my_details')
			map['posts'] ||= []
            map['posts'] << {

                'status' => event.get('status'),
                's_id' => event.get('s_id')
            }
        event.cancel()"
        push_previous_map_as_event => true
        timeout => 80
    }
}

```

Now it's Working.

Human Error 🙂

---

<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:** [June 24, 2022, 4:58am UTC](https://discuss.elastic.co/t/logstash-pipeline-for-nested-data-from-sql/305688/4 "2022-06-24T04:58:02Z")

</div>

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