toluaina/pgsync

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

virtualaidev opened this issue · 0 comments

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?