how can i create dynamic json data to hit my rest client

Question

I'm a beginner in data integration tool Pentaho.

My data base table hase all columns together. Now I want to create a JSON from that table data in order to hit my REST client.

My REST client accepts JSON like this, so I need to prepare the same.

{
 "firstName" : "xyz",
  "lastName" : "sdf",
  "birthDate" : "1977-07-07",
  "email" : "hj@dev4life.com",
  "phones" : [ {
    "number" : "123456",
    "phoneType" : "PRIMARY"
  } ]
}

enter image description here

Hi @Dmitriy Popov my question is like you have you have some data in your table eg name: employee with no relations employee_name employee_addresline1 employee_city employee_pincode if i'm using directly jsoninput option by pentaho the the I'm getting json like this {"data":[{"employee_name" :"sam", "employee_addresline1": "CB-124 street lory", "employee_city":"NY", "employee_pincode":"15585"}]} — Himanshi, Jul 19 '19 at 11:29
I can use data value to hot my rest client however i need json like this {"employee_name" :"sam", "address":{ "employee_addresline1": "CB-124 street lory", "employee_city":"NY", "employee_pincode":"15585"} } — Himanshi, Jul 19 '19 at 11:29
For complex JSON structures you're better off using Modified Java Script Value step. Just be careful on using For iterators in Pentaho Javascript, they work the same, but they don't 'perform' the same. — Cristian Curti, Jul 19 '19 at 12:08
Hi, If you feel an answer solved the problem, please mark it as 'accepted' by clicking the green check mark. This helps keep the focus on older SO which still don't have answers. — Smile, Mar 04 '20 at 07:46

score 2 · Answer 1 · answered Jul 23 '19 at 19:29

You are almost there with the JSON Output step. the problem is that JSON Output step doesn't handle nested data structures well, so you might want to handle phones and the other information separately and then merge the results.

Below is an example KTR to handle your task:

JSON Output step: process phones

In the first JSON Output step, set up the following

under Fields Tab, add two fields: number and phoneType. The new field phones after this step is:

{"phones":[{"number":123456.0,"phoneType":"PRIMARY"}]}

JSON Output step:

do the similar to the above step except the following:

In General tab, set Json bloc name = data, Output Value = outputValue

In Fields tab, add the fields firstName, lastName, birthDate and email

The new field called outputValue added from this step is:

{"data":[{"firstName":"himanshi","lastName":"joshi","birthDate":"1997-07-24","email":"hj@test.com"}]}

Note: you should not retrieve and add phones into the JSON output here since the nested data structure will become a single String value in the final output.

JSON Input step: retrieve $.data[0]

As labelled in the Step name, we use JSON Input step to retrieve $.data[0] from the field outputValue, so it becomes:

{"firstName":"himanshi","lastName":"joshi","birthDate":"1997-07-24","email":"hj@test.com"}

In File tab: select Source is from a previous step and Select field = outputValue

In Fields tab, add a new field: Name = rest_client_data and Path = $.data[0]

Replace in string step: merge phones

Use the Replace in string step to merge two JSON strings: phones and rest_client_data.

Basically, it replaces the leading brace { of phones with a comma and then replaces the trailing } of rest_client_data with the updated phones

Select values step

To keep only the needed field: rest_client_data

{"firstName":"himanshi","lastName":"joshi","birthDate":"1997-07-24","email":"hj@test.com","phones":[{"number":123456.0,"phoneType":"PRIMARY"}]}

score 1 · Answer 2 · answered Jul 22 '19 at 11:16

1

Best way to create JSON from a tabular data structure is via the Javascript step. JSON input is meant to work the other way around: given a JSON data field, extract individual values.

answered Jul 22 '19 at 11:16

nsousa

4,448
1
10
15