Ingest SQL relationships as nested documents

Elastic Stack Logstash
1

Hi everyone, I’m trying to figure out a way to index my Postgres data into elasticsearch. I have a user table, and a purchase table with a user_id column linking to a user. I want to have a elasticsearch document for each user and within a nested document with each purchase that this user has made.

I’m planning to use the user_id as the elasticsearch document id reference, however:
1- I can’t figure a way to create nested objects with logstash input.
2- Can’t find a way to add multiple purchases fields to a same document. (It’s being overwritten)

2

Have you considered denormalising your model and store each order as a separate document with the relevant customer information added to each document?

3

Hi i will probably try that. Still I wonder if there isn't another way of doing this.

Thank you very much

4

Hey mwmouawad,

I have not done it myself however I think you can get postgres to create the nested json for you, using row_to_json, you might want to hide your query behind a view so logstash just queries one view and gets the nested json back. If this does work please let me know.

Alternatively you can use the jdbc streaming filter. Your logstash input query gets all the users, then using the jdbc filter it queries postgres for all transactions for that user and adds it to the event.

5

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