# Converting columns in SQL table to nested datatype using Logstash JDBC plugin

**URL:** https://discuss.elastic.co/t/converting-columns-in-sql-table-to-nested-datatype-using-logstash-jdbc-plugin/81871
**Category:** Logstash
**Created:** [April 10, 2017, 8:56pm UTC](https://discuss.elastic.co/t/converting-columns-in-sql-table-to-nested-datatype-using-logstash-jdbc-plugin/81871 "2017-04-10T20:56:07Z")
**Posts on this page:** 2
**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: [April 10, 2017, 8:56pm UTC](https://discuss.elastic.co/t/converting-columns-in-sql-table-to-nested-datatype-using-logstash-jdbc-plugin/81871/1 "2017-04-10T20:56:07Z")

</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: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 8, 2017, 9:03pm UTC](https://discuss.elastic.co/t/converting-columns-in-sql-table-to-nested-datatype-using-logstash-jdbc-plugin/81871/2 "2017-05-08T21:03:06Z")

</div>

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