# Converting Un-Pivoted SQL Data to Nested Datatype using Logstash JDBC Input

**URL:** https://discuss.elastic.co/t/converting-un-pivoted-sql-data-to-nested-datatype-using-logstash-jdbc-input/88301
**Category:** Logstash
**Created:** [June 5, 2017, 4:40pm UTC](https://discuss.elastic.co/t/converting-un-pivoted-sql-data-to-nested-datatype-using-logstash-jdbc-input/88301 "2017-06-05T16:40:53Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![Rory](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [June 5, 2017, 4:40pm UTC](https://discuss.elastic.co/t/converting-un-pivoted-sql-data-to-nested-datatype-using-logstash-jdbc-input/88301/1 "2017-06-05T16:40:54Z")

</div>

Hi,

I have a table in SQL Server which looks like this:

The data is in an un-pivoted format:

CustomerID, AddressNumber, AddressValue  
1, 1, a  
1, 2, b  
1, 3, c  
1, 4, d  
1, 5, e  
2, 1, f  
2, 2, g  
2, 2, h  
2, 2, i  
2, 2, k  
...

I can easily pivot the table to look like:  
CustomerID, Address1, Address2, Address3, Address4, Address5  
1, a, b, c, d, e  
2, f, g, h, i, k  
...

I was wondering if there is a way while using logstash, to convert each row in this pivoted table into a document which looks like:

```
{
  "CustomerID":1,
  "Address": 
  [
    {
      "AddressID":1,
      "AddressValue": "a" 
    },
    {
      "AddressID":2,
      "AddressValue": "b"
    },
    {
      "AddressID":3,
      "AddressValue": "c"
    },
    {
      "AddressID":4,
      "AddressValue": "d"
    },
    {
      "AddressID":5,
      "AddressValue": "e"
    }
  ]
}

```

Thanks.

---

<div class="post-metadata">

### Author: ![Rory](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [June 8, 2017, 6:13pm UTC](https://discuss.elastic.co/t/converting-un-pivoted-sql-data-to-nested-datatype-using-logstash-jdbc-input/88301/2 "2017-06-08T18:13:27Z")

</div>

Bump.

---

<div class="post-metadata">

### Author: ![Rory](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [June 16, 2017, 2:44pm UTC](https://discuss.elastic.co/t/converting-un-pivoted-sql-data-to-nested-datatype-using-logstash-jdbc-input/88301/3 "2017-06-16T14:44:58Z")

</div>

Is it possible to achieve this using Logstash?

Thanks.

---

<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 14, 2017, 2:45pm UTC](https://discuss.elastic.co/t/converting-un-pivoted-sql-data-to-nested-datatype-using-logstash-jdbc-input/88301/4 "2017-07-14T14:45:27Z")

</div>

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