I have a requirement where I need to fetch records from multiple tables. The primary table is having one-to-many relationship to other tables.
My data source is Oracle DB. Oracle db is having the specified tables. One called Student other one is Subjects.
For sample, I have a Student Table where "Student_Id" is the Primary Key and other columns like firstname, lastName etc. Each student have registered for multiple subjects so we have student_id is the foreign key to the Subjects table. Subjects table is having subject name, Status, Teacher Name etc i.e. a student can have multiple subjects. In the Student table, I have students phone numbers like his home phone, cell phone and parent's contact number. These 3 numbers should come as one object under student node as given below.
So requirement is to show all the students from the student table and their corresponding subjects for each student as an array and the Phone Numbers for each student. The output should be in Json format.
I have given the structure below. Please let me know how to achieve this using Pentaho data integration tool. I am very much new to this technology.
{
"data": [
{
"Student_ID": "1",
"FirstName": "fname1",
"LastName": "lname1",
"subjects": [
{
"Name": "Physics",
"Status": "Active",
"Teacher": "Teacher1"
},
{
"Name": "History",
"Status": "InActive",
"Teacher": "Teacher2"
}
],
"Phone": {
"Home": "123456",
"Cell": "3456790",
}
},
{
"Student_ID": "2",
"FirstName": "fname2",
"LastName": "lname2",
"subjects": [
{
"Name": "Geography",
"Status": "Active",
"Teacher": "Teacher1"
},
{
"Name": "English",
"Status": "InActive",
"Teacher": "Teacher2"
}
],
"Phone": {
"Home": "123456",
"Cell": "3456790",
}
}
]
}