topic Re: Convert to json object in Talend Studio

Convert to json object

heshkaru — Mon, 21 Mar 2022 15:00:03 GMT

heshan|23|gcp|95|name1|40

heshan|23|gcp|95|name2|41

heshan|23|aws|90|name3|42

heshan|23|aws|90|name4|46

Is it possible to convert the above result set to a JSON Object like below.

{

"name":"heshan",

"age":23,

"experience":[

{

"course":"gcp",

"marks":"95",

"lecturer":[

{

"lname":"name1",

"lage":40

{

"lname":"name2",

"lage":41

}

]

{

"course":"aws",

"marks":"90",

"lecturer":[

{

"lname":"name3",

"lage":42

{

"lname":"name4",

"lage":46

}

]

}

]

}

Re: Convert to json object

Anonymous — Tue, 22 Mar 2022 00:58:27 GMT

You can do this with a tWriteJSONField component. I have quickly put tan example together using your data. It is configured like this....

After this, click the "Configure JSON Tree" button and configure like this...

The output looks like this....

{

"name":"heshan",

"age":23,

"experience":[

{

"course":"gcp",

"marks":95,

"lecturer":[

{

"lname":"name1",

"lage":40

{

"lname":"name2",

"lage":41

}

]

{

"course":"aws",

"marks":90,

"lecturer":[

{

"lname":"name3",

"lage":42

{

"lname":"name4",

"lage":46

}

]

}

]

}

Re: Convert to json object

heshkaru — Tue, 22 Mar 2022 07:19:20 GMT

@Richard Hall

Thank you that sorted it.

Re: Convert to json object

heshkaru — Thu, 24 Mar 2022 09:34:13 GMT

Hi @Richard Hall small question,

So i have to use 3 textractjson blocks to extract the data.

Issue is if one of the array blocks is null then the whole json becomes null. Is there a specific way to overcome this, or do I need to use conditions to check whether that element is empty or null and proceed.

{

"site_id": "003",

"purchase_order_details": [

{

"item": "2285542",

"purchase_order_details_notes": [

{

"note_line": 1

}

]

}

"purchase_order_allowances": [

{

"amount": 23.5

}

]

}

Re: Convert to json object

Anonymous — Thu, 24 Mar 2022 15:44:11 GMT

That is a different JSON structure to the one you originally asked about. Can you give an example of a null array block that you are dealing with? Is there supposed to be another array inside the "purchase_order_allowances" array?

Re: Convert to json object

heshkaru — Thu, 24 Mar 2022 16:16:42 GMT

Hi,

purchase_order_details is a nested array inside the json object.

purchase_order_allowances is an array inside the json object.

If all the details are available the process is working. But if either one the array block is null the output is becoming null.

{

"site_id": "003",

"purchase_order_details": [

{

"item": "2285542",

"purchase_order_details_notes": [

{

"note_line": "1"

}

]

}

]

}

For example, If we use the above json format the job will fail. (Since inside of textractjsonfield we are converting the object into sub types)

ex:

purchase_order_details (Object) -> item (String) and purchase_order_details_notes(Object)

Re: Convert to json object

Anonymous — Thu, 24 Mar 2022 18:02:48 GMT

The JSON you provided in your first response was this ....

{

"site_id":"003",

"purchase_order_details":[

{

"item":"2285542",

"purchase_order_details_notes":[

{

"note_line":1

}

]

}

"purchase_order_allowances":[

{

"amount":23.5

}

]

}

In the JSO you show above, it is exactly the same but without the "purchase_order_allowances" array. This array sits at the same level as the "purchase_order_details" array. Since you have two arrays at the same level (and one might be missing) you will need to restructure your job. The first tExtractJSONFields component should be extracting both arrays (as sub JSON objects), you will then need to split the job (potentially with a key to link the data) and extract the remaining data in 2 paths. Afterwards, rejoin the extracted data using the key.

Re: Convert to json object

heshkaru — Fri, 25 Mar 2022 05:55:20 GMT

Hi any working solution,

Not sure about the whole idea. Is it possible to create 2 separate json objects and merge it into 1?

Re: Convert to json object

Anonymous — Fri, 25 Mar 2022 13:25:11 GMT

I don't have a working example I can give. But essentially this is what I am saying. In your first tExtractJSONFields component you need to extract "purchase_order_details" and "purchase_order_allowances" as JSON sub-sections and also extract the "site_id". Then go to a tMap and send the "purchase_order_details" column (with "site_id") to one output and the "purchase_order_allowances" (with "site_id") to another output. Then from those two outputs, configure tExtractJSONFields to extract what you need from each and store the rows (including the "site_id") in tHash components.

Then, in a subjob afterwards, simply join the outputs of that using tHash components and a tMap. Join on "site_id".

Why would you need to create JSON here? You are extracting it.

Re: Convert to json object

heshkaru — Sat, 26 Mar 2022 04:17:58 GMT

We are checking an API for legacy and a new System. We are particularly mapping from one json to its legacy json body with exact values(Keys are changed). Thats why we are converting from 1 json to another.