topic Re: Create a document with loops in Talend Studio

Create a document with loops

Alpha549 — Fri, 15 Nov 2024 23:57:03 GMT

Hello everyone,

Let's imagine we have these fictional data in a tFixedFlow (but this will actually come from a database) :

I want to create an API.

We could get this JSON file by default, converting the data lines returned by the database / tfixedflow here into a document : LET'S NAME IT CONTENT 1

[

{

"ID_Ent": 1,

"Name": "Alpha",

"Skill": "Small Fire",

"Weakness": "Water"

{

"ID_Ent": 1,

"Name": "Alpha",

"Skill": "Medium Fire",

"Weakness": "Air"

{

"ID_Ent": 2,

"Name": "Bravo",

"Skill": "Big Fire",

"Weakness": "Ice"

{

"ID_Ent": 1,

"Name": "Alpha",

"Skill": "Small Cyclone",

"Weakness": "Poison"

}

]

However, I wonder if it's possible to get this json content : LET'S NAME IT CONTENT 2

[

{

"ID_Ent": 1,

"Name": "Alpha",

"Skill": ["Small Fire", "Medium Fire", "Small Cyclone"],

"Weakness": ["Water", "Air", "Poison"],

{

"ID_Ent": 2,

"Name": "Bravo",

"Skill": "Big Fire",

"Weakness": "Ice"

]

Or even this one : LET'S NAME IT CONTENT 3

[

{

"ID_Ent": 1,

"Name": "Alpha",

"Skill": "Small Fire",

"Skill": "Medium Fire",

"Skill": "Small Cyclone",

"Weakness": "Water",

"Weakness": "Air",

"Weakness": "Poison",

{

"ID_Ent": 2,

"Name": "Bravo",

"Skill": "Big Fire",

"Weakness": "Ice"

]

Is it possible to generate CONTENT 2 ? What about CONTENT 3 ? How ?

Thank you in advance ! 🙂

Re: Create a document with loops

gjeremy1617088143 — Thu, 15 Jul 2021 14:27:30 GMT

Hi , here the best I can get with few components :

[{"Id_ent":1,"Name":"Alpha","Skill":["Small Fire","Medium Fire","Small Cyclone"],"Weakness":["Water","Air","Poison"]},{"Id_ent":2,"Name":"Bravo","Skill":["Big Fire"],"Weakness":["Ice"]}]

I add a new column named CUMUL with X as value in tfixedflowInput

in the schema i set Id_Ent as an Int and everything else as String

then i use a tSortRow and I sort Id_ent by num asc

then I use a tDenormalize I Set Skill and Weakness as column to denormalize with "\",\"" as Delimiter and Merge same value check

then in a tWrite JSONField :

JSON TREE

rootTag

sub element of rootTag root as loop element

sub element of root :ID_Ent

sub element of root Name

Sub element of root Skill with attribute class static value array

Sub element of root Weakness with attribute class static value array

and a group by the CUMUL column on the component options and remove root node checked

then i use a tReplace Search "\\" replace with "" no options checked.

Send me Love and Kudos

Re: Create a document with loops

gjeremy1617088143 — Thu, 15 Jul 2021 15:00:28 GMT

you can do (your string).replaceAll("\\[(?=([a-zA-z \"]+\\]))","")

.replaceAll("(?<=(\\:[a-zA-z \"]{1,100}))\\]","")

to replace all [ and ] when you have unique value

of course it's only work with letters and space in the value

here the result :

[{"Id_ent":1,"Name":"Alpha","Skill":["Small Fire","Medium Fire","Small Cyclone"],"Weakness":["Water","Air","Poison"]},{"Id_ent":2,"Name":"Bravo","Skill":"Big Fire","Weakness":"Ice"}]

Re: Create a document with loops

Alpha549 — Thu, 15 Jul 2021 20:10:50 GMT

Thanks a lot ! It worked fine !

I still have more questions :

1) If I erase a value in the tFixedFlow (for example a weakness), is get "null" in the weakness values :

The same if I use an empty string, I get "" instead of "null".

Is there a way to simply delete this value from the [ ... ]?

EDIT : I did it using a tReplace, replacing :

",\"null\"]" by "]"
",\"null\"," by ","
"[\"null\"," by "["
"[\"null\"]" by "[]"
Idem for empty string

However, is there another way (just to have several methods in my toolbox) ? 🙂

2) If I configure my tREST_RESPONSE on String, the result is fine. Using a tREST_RESPONSE, Is it possible to return an XML or a JSON ? I think that here we made the choice of returning a json string in any case but is there a way to allow the client to choose what it wants ? For example if the Accept client header is application/xml or application/json ?

In any case, if not possible, I could let my tREST_RESPONSE configuration on String, knowing that this string is a json.

Re: Create a document with loops

gjeremy1617088143 — Fri, 16 Jul 2021 07:11:07 GMT

Hi, in this scenario I manipulate the String to make multiple loop in tWriteJsonField, I hijack the component wich is not made to work like that, so you have to make string manipulation to get what you want,

as you can see it's not very difficult and don't require a lot of components.

you can do a really complex regular expression to delete the null values but in your case tReplace will do the work easily.

Rest Webservice are usaly made to work with JSON , but you can also return a xml response.

In tRestClient you Have globalMap : ((java.util.Map<String,java.util.List<String>>)globalMap.get("tRESTClient_1_HEADERS")) wich permit you to know the header of your request , so you can drive your response in Json or xml depending of the value application/xml or application/json , but you have to make a flow that transform your database input into an xml file for the xml part.

Re: Create a document with loops

Alpha549 — Fri, 16 Jul 2021 13:48:52 GMT

Thanks a LOT for your help gjeremy1617088143

You perfectly responded to my questions 🙂

See you soon maybe !

Re: Create a document with loops

gjeremy1617088143 — Fri, 16 Jul 2021 14:35:23 GMT

I Hope it's real data, Ultima was my favorite materia ;).