Postgres to Json. Pentaho 7.0 ( Data Integration)

You can create and add to JSON objects with the standard Javascript and then convert them to strings when you output it to the stream.

Variables exist for as long as the step runs, so you can declare them in a separate start script tab and then add to them for each row. This isn't a very obvious function, but you may already be aware of it. Right-click next to the starting tab to create a new tab. Right-click a tab to set it to "Start Script" (runs before the first row) or "Transform" script (runs for each row).

I tried using an End script as well, but it only runs after the last row, so I didn't have an output row anymore. Instead, I put a Detect Last Row in Stream before the Javascript step to mark the last row.

This goes in the Start Script:

var jsonDoc = []; //create array var jsonOutput = "default"; // this will be the output string var currentYear = 0; 

And this in the Transform script:

if (currentYear < YearField) { // year changed, add the previous one to the main array if (currentYear > 0) { jsonDoc.push(oneYear); } currentYear = YearField; // set up the new year var oneYear = {}; oneYear.year = YearField; oneYear.users = []; } // add the user to the current year array var oneUser = {}; oneUser.user = UserField; oneUser.created = CreatedField; oneYear.users.push(oneUser); // At the last row, add the current year and //create the string variable that will be your output field if (LastRow==true) { jsonDoc.push(oneYear); jsonOutput = JSON.stringify(jsonDoc); }