Schema with Nested Translation Data Sync from PostgreSQL to Elasticsearch

[lxup]

PGSync version: 3.2.0

Postgres version: 15.1

Elasticsearch/OpenSearch version: 8.15.0

Redis version: 7.2.5

Python version: 3.9

Problem Description:

I have two PostgreSQL tables, tmdb_movie and tmdb_movie_translation, that I'd like to sync with Elasticsearch using PGSync. The goal is to create two Elasticsearch indexes, movies_en and movies_fr, with the translation data (tmdb_movie_translation) nested directly within the parent movie data (tmdb_movie).

Table Structure:

• tmdb_movie contains general movie information (e.g., id, original_title, release_date, etc.).
• tmdb_movie_translation contains language-specific information (e.g., title, overview, poster_path, etc.) and is linked to tmdb_movie via a foreign key on movie_id.

Desired Elasticsearch Index Structure:

• For each movie in the tmdb_movie table, I want to include the corresponding translation data from tmdb_movie_translation directly in the parent document under fields like title, overview, and poster_path.
• The movies_en index should contain English translations, and the movies_fr index should contain French translations.

I'm looking for guidance on how to configure PGSync to achieve this nested structure. Thx a lot (Im a student so sry if my explanation are quite inexact) ! 😁

Error Message (if any):