Postgres to Json. Pentaho 7.0 ( Data Integration)

Question

I make a query to a database of postgres and I bring two fields, "USER" and "CREATED" (DATE)

I extract the year from the creation date, and then it is traversing the records and according to the year and the user create the new json object

And I would like to generate a json with the following structure.:

[
   {year:2015,
        users[
             {
              user:"Ana"
              created: 4
             },
             {
              user:"Pedro"
              created: 7
              }
             ]},
    year:2016,
        users[
             {
             user:"Ana"
             created: 4
             },
             {
             nombre:"Pedro"
             created: 7
             }
            ]}
]

I create a modification with "Modified Java Script Value", I have several doubts, I want to go through an array for each year and each user but I see that in the data below only I can save it as:NUMBRE,STRING,DATA,BOOLEAN,INTEGAR,BIGNUBER,BINARY,TIMESTAMP,INTERNET-ADDRESS.

I do not know how I can generate my json with arrays and objects inside objects.

Cyrus · Accepted Answer · 2017-04-27T16:36:00.763

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);
}

You could've call `putRow(outRow)` function in end script to produce the final row. The function takes an array, representing your output row as an argument. One can use `outRow = createRowCopy(getOutputRowMeta().size())` to copy the input row, then set the additional fields e.g. like this: `outRow[getOutputRowMeta().size() - 1] = _step_.getValueFromJScript(jsonOutput, 1)` — Andrei Luksha, May 05 '17 at 11:39

score 0 · Answer 2 · answered May 10 '17 at 20:02

0

You can use Json Input / Ouput, the javascript step is not recommended when you have a lot of data, it is better to use the steps that the kettle brings by default or a user defined java class, greetings

answered May 10 '17 at 20:02

Jorge Dieguez Perez

187
8

Postgres to Json. Pentaho 7.0 ( Data Integration)

2 Answers2

Linked