# Format nested json with Sql joins
**URL:** https://discuss.elastic.co/t/format-nested-json-with-sql-joins/31405
**Category:** Logstash
**Created:** [September 30, 2015, 12:30pm UTC](https://discuss.elastic.co/t/format-nested-json-with-sql-joins/31405 "2015-09-30T12:30:27Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@Alexcopquin1](https://discuss.elastic.co/u/Alexcopquin1)
#### Post date: [September 30, 2015, 12:30pm UTC](https://discuss.elastic.co/t/format-nested-json-with-sql-joins/31405/1 "2015-09-30T12:30:27Z")
Hi,
We want to hydrate an ES cluster with posgres data and are trying logstash.
The input sql looks something like
Select mem.id, mem.email, mem.first\_name,
addr.id as address\_id, addr.city, addr.state, addr.zip
From members mbr
Left outer join addresses address
on mbr.id = addr.member\_I'd
Order by mbr.I'd
Which gives me a rowset like:
id email. first\_name. address\_id city state zip
1234567, [john@doe.com](mailto:john@doe.com), john, 1111, Corona, NY, 11106
1234567, [john@doe.com](mailto:john@doe.com), john, 2222, Hollywood, CA, 90210
1234567, [john@doe.com](mailto:john@doe.com), john, 3333, Hicksville, NY, 11801
and we want the json inserted in ES to look something like:
{
id : 1234567,
email: "john@doe.com",
first\_name: "john",
addresses: [
{
address\_id: 1111,
city: "Corona",
state: "NY" ,
zip: "11106"
},
{
address\_id: 2222,
city: "Hollywood ",
state: "CA",
zip: "90210"
},
{
address\_id: 3333,
city: "Hicksville",
state: "NY",
zip: "11801"
}]
}
We are looking into using the aggregate filter plug-in but not quite understand the documentation.
How do we go about generating the "addresses" array in the JSON payload?
Thanks
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [November 7, 2015, 10:38pm UTC](https://discuss.elastic.co/t/format-nested-json-with-sql-joins/31405/2 "2015-11-07T22:38:03Z")
Hi,
I don't think aggregate plugin could answer your need.
But maybe the different jdbc input plugins could help you so that all results of your sql query are stored in only one document.
Then you can custom the result document format, using mutate and ruby filters.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 5:23am UTC](https://discuss.elastic.co/t/format-nested-json-with-sql-joins/31405/3 "2017-07-06T05:23:29Z")