Unique record with nested JSON objects

asked 2021-02-17 21:17:58 -0500

Raj gravatar image

updated 2021-02-17 21:18:36 -0500

I have Input DataSet from JDBC Consumer , very similar to classic example of employee table and department table.

Requirement is to generate a JSON for each department as below. So, for 'N' departments, expected output is 'N' records in JSON format.

{ "Dept ID": "1", "Dept Name": "D1", "Employee" [

              {"Emp ID": "1", "Emp Name": "Name101", "Emp Addr": "Addr101"},
              {"Emp ID": "2", "Emp Name": "Name102", "Emp Addr": "Addr102"}
             ]

}

Tried Approach:

1) Used Department as JDBC Consumer


2) Used Employee as JDBC Lookp

As we are going to have 1 to many records returned for each department, tried using 'Field Zip' processor. This has limitation on using 2 fields at a time. So, not ideal when we have to lookup more than 2 fields and combine then based on list Index. Moreover, this doesn't seem a feasible solution if more nested JSON is to be generated for each employee ( think about JSON object for different address types, home/office telephone etc.)

Any suggestions on how we can approach solution for this problem?

edit retag flag offensive close merge delete