Postgres 到 Json.Pentaho 7.0(数据集成) [英] Postgres to Json. Pentaho 7.0 ( Data Integration)

问题描述

我对 postgres 数据库进行了查询，并带来了两个字段，USER"和CREATED"(日期)

我从创建日期中提取年份，然后遍历记录并根据年份和用户创建新的json对象

我想生成一个具有以下结构的json.:

<预><代码>[{年:2015，用户[{用户:安娜"已创建:4},{用户:佩德罗"已创建:7}]},年份:2016,用户[{用户:安娜"已创建:4},{名称:佩德罗"已创建:7}]}]

我使用Modified Java Script Value"创建了一个修改，我有几个疑问，我想为每年和每个用户遍历一个数组，但我看到在下面的数据中只有我可以将其保存为:NUMBRE，字符串、数据、布尔值、整数、BIGNUBER、二进制、时间戳、互联网地址.

我不知道如何使用对象内的数组和对象生成我的 json.

解决方案

您可以使用标准 Javascript 创建并添加到 JSON 对象，然后在将其输出到流时将它们转换为字符串.

只要步骤运行，变量就会一直存在，因此您可以在单独的启动脚本选项卡中声明它们，然后为每一行添加它们.这不是一个很明显的功能，但您可能已经意识到了.右键单击起始选项卡旁边以创建新选项卡.右键单击选项卡以将其设置为启动脚本"(在第一行之前运行)或转换"脚本(为每一行运行).

我也尝试使用 End 脚本，但它只在 最后一行之后运行，所以我不再有输出行了.相反，我在 Javascript 步骤之前放置了一个检测流中的最后一行来标记最后一行.

这在启动脚本中:

var jsonDoc = [];//创建数组var jsonOutput = "默认";//这将是输出字符串无功当前年 = 0;

这在转换脚本中:

if (currentYear 

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.

解决方案

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

这篇关于Postgres 到 Json.Pentaho 7.0(数据集成)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！