We have setup source connector (io.debezium.connector.postgresql.PostgresConnector) on postgersql db which will listen to 3 tables and forward data changes to Kafka topic.
And created streams for this 3 topics. Below are list of streams running fine and listening to tables.
Stream "Stream_Project" created from topic projects
+-----------+-------------+
|ProjectId |ProjectName |
+-------------------------+
|1 |Project 1 |
|2 |Project 2 |
+-----------+-------------+
Stream "Stream_Skill" data created from topic skills
+-----------+-------------+-------------+-------------+
|SkillId |ProjectId |SkillName |Proficiency |
+-----------+-------------+-------------+-------------+
|1 |1 |Skill 101 |L1 |
|2 |1 |Skill 102 |L2 |
|3 |2 |Skill 201 |L1 |
|4 |2 |Skill 202 |L2 |
+-----------+-------------+-------------+-------------+
Stream "Stream_Tech" data created from topic techs
+-----------+-------------+-------------+
|TechId |ProjectId |TechName |
+-----------+-------------+-------------+
|1 |1 |Tech 101 |
|2 |1 |Tech 102 |
|3 |2 |Tech 201 |
|4 |2 |Tech 202 |
+-----------+-------------+-------------+
Now I am trying to join all the stream to get result as below in new stream/table which I can Sink to another PostgresSql db. But I am not sure how I can get result as below from joining all the 3 stream data.
+-----------+-------------+--------------------------------------------------------------------------------------------+--------------------------------------------------+
|ProjectId |ProjectName |Skills |Techs |
+-----------+-------------+--------------------------------------------------------------------------------------------+--------------------------------------------------+
|1 |Project 1 |[{"SkillName":"Skill 101","Proficiency":"L1"},{"SkillName":"Skill 102","Proficiency":"L2"}] |[{"TechName":"Tech 101"},{"TechName":"Tech 102"}] |
|2 |Project 2 |[{"SkillName":"Skill 201","Proficiency":"L1"},{"SkillName":"Skill 202","Proficiency":"L2"}] |[{"TechName":"Tech 201"},{"TechName":"Tech 202"}] |
+-----------+-------------+--------------------------------------------------------------------------------------------+--------------------------------------------------+
Can anyone guide me or provide way to generate output as above in ksqlDB
Thanks