toluaina / pgsync

Postgres to Elasticsearch/OpenSearch sync
https://pgsync.com
MIT License
1.1k stars 172 forks source link

getting column from the through_tables to be added to the children columns #440

Open virtualaidev opened 1 year ago

virtualaidev commented 1 year ago

PGSync version: 2.5.0

Postgres version: debezium/postgres:15

Elasticsearch version: docker.elastic.co/elasticsearch/elasticsearch:8.7.0

Redis version: 6.2.6

Python version: 3.7

Problem Description:

I have tables structured like the below movie id name
1 Movie 1
2 Movie 2
region id name
1 US
2 UK
movies_regions movie_id region_id date
1 1 2023-01-01 00:00:00
1 2 2023-01-10 00:00:00

I would like to translate this into

[
    {
        "region": "US",
        "date": "2023-01-01 00:00:00"
    },
    {
        "region": "UK",
        "date": "2023-01-10 00:00:00"
    }
]

I've tried the idea similar to the below, but does not seem to have a way

{
    "database": "movies",
    "index": "movies",
    "nodes": {
    "table": "movie",
    "schema": "public",
    "columns": [
      "id",
      "name"
    ],
    children: [
        {
            "table": "movies_regions",
            "schema": "public",
            "columns": [ "name", "date" ],
            "transform": {
                "rename": {
                    "name": "region"
                }
            }
            "label": "Releases",
            "relationship": {
                "variant": "object",
                "type": "one_to_many",
                "through_tables": [ "region" ]
            }
        }
    ]
}

is something that I missed to make this json output possible?