Format nested json with Sql joins


(Alexander B Copquin) #1

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, john, 1111, Corona, NY, 11106
1234567, john@doe.com, john, 2222, Hollywood, CA, 90210
1234567, 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


(Fabien Baligand) #2

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.


(system) #3