topic Re: Json list array and not json string array using twritejson in Data Quality

Json list array and not json string array using twritejson

TRandy — Fri, 15 Nov 2024 22:18:52 GMT

Hi, I would like to build an array of objects using twritejsonFields as below.

But when i set the type list as the transaction field type and run the job.

I get <<cannot convert from string to list>> .

yet I need transactions to be of type list and not string

Please can you help me

Re: Json list array and not json string array using twritejson

Anonymous — Thu, 01 Dec 2022 03:37:50 GMT

Hello @Tim Randy ,

Unfortunately, tWriteJSONField component can only output a String Type object instead of List object.

To achieve your goal, please add another tJavaRow component after the tWriteJSONField to convert the json Array string to the List object,

Best regards

Aiming

Re: Json list array and not json string array using twritejson

TRandy — Thu, 01 Dec 2022 07:20:36 GMT

Hi @Aiming Chen ,

I used the tJavaRow component and routines as you suggested in this topic

https://community.talend.com/s/feed/0D55b000082Tpq3CAC

and I got this.

Unfortunately the result is not as expected

Re: Json list array and not json string array using twritejson

Anonymous — Thu, 01 Dec 2022 07:35:08 GMT

@Tim Randy , what's your expected results? could you please give an example?

Re: Json list array and not json string array using twritejson

TRandy — Thu, 01 Dec 2022 09:07:50 GMT

@Aiming Chen

ok my twriteJsonFields component gives me 02 output fields ids & transactions (which should be a json array list and not a json array string). The transaction field is grouped by ids (type string).

i have used tjavarow to convert json array list from json array string

here is the output transaction field :

"transaction" provides by twriteJsonFields composant :

"[{

"Amount" : 5000,

"code" : ["Tx-1000", "Tx-1001"],

"date" : "2001-11-03T15:38:52.330+01:00",

"data" : {

{ "id" : "1001",

"validated" : true},

{ "id" : "1002",

"validated" : true}

}

{

"Amount" : 10000,

"code" : ["Tx-1020", "Tx-1022"],

"date" : "1991-08-31T11:30:52.330+01:00",

"data" : {

{ "id" : "0099",

"validated" : false},

{ "id" : "0098",

"validated" : false}

}

to test the solution you proposed in the link mentioned above. I defined the routines as follows

Re: Json list array and not json string array using twritejson

Anonymous — Thu, 01 Dec 2022 09:49:36 GMT

@Tim Randy , tWriteJsonField can only output 1 String Type column which you can define it in it's Output Column settings as the below

Re: Json list array and not json string array using twritejson

TRandy — Thu, 01 Dec 2022 10:06:42 GMT

I know that @Aiming Chen

when creating the topic I have the captures of my configurations.

The problem is that the output transaction field I get with my twriteJson is a string and not a list. You suggested I add a tjavarow to my work, which I did.

Unfortunately, I still don't get the right result.

The description of the routines used by my tJavarow is more in line with the details you asked for.

Here is my job

Re: Json list array and not json string array using twritejson

Anonymous — Thu, 01 Dec 2022 11:47:23 GMT

I have looked at this question and the question you have referred to where @Aiming Chen was suggesting ideas about creating arrays. In that question (and in your screenshot above), the arrays are being produced but contains the name of the object type followed by a hash code. This is standard Java functionality when the "toString()" method is not overwritten to include human readable information about the object. Since JSON is essentially just a String, everything that is represented by it needs to be human readable. So you may be close with this, but just need to output something a bit more meaningful to you.

However, on reading through this question, I am afraid I am not sure what your input data looks like and what the output is expected to look like. I suspect that this may be achievable in an easier way.

Re: Json list array and not json string array using twritejson

TRandy — Thu, 01 Dec 2022 14:18:02 GMT

Thank you for your feedback @Richard Hall ,

my input data, its types and the configuration of my twriteJsonFileds is given on these screenshots.

My component builds the Json array well except it doesn't have the right type. I just want it to be a list and not a string.

in short, I just need the transaction that is json array that I create to be just of type list and not string.

Re: Json list array and not json string array using twritejson

Anonymous — Thu, 01 Dec 2022 14:58:00 GMT

I think you mean an array and not a list. I don't mean to be picky, but I sense that this is causing a bit of confusion. I am guessing that your output JSON for this data will need to look something like this....

{"data": [ "val1", "val2", "val3" ]}

....the format won't exactly match and I am not including other fields. If this is the case, it is a JSON Array you need. Depending on your input data and output format required, there will be different ways to achieve this. But I will need to see an example of the input data (not the actual data) and the intended output JSON.

Re: Json list array and not json string array using twritejson

TRandy — Thu, 01 Dec 2022 15:32:50 GMT

Hi @Richard Hall ,

input data 👇

output desired

you notice here that transactions is a json array so its value is not between "".

Re: Json list array and not json string array using twritejson

Anonymous — Thu, 01 Dec 2022 16:02:09 GMT

Fundamentally you have to treat your input data as Strings, Numbers, Booleans and nulls, contained in arrays and objects (JSON Objects). JSON is essentially just text. As such, you will need to extract this input data into rows like this.....

Cash, Code, DateAt, Identifiant, Validate

5000, Tx-1000, 2001-11-03T15:38:52.330+01:00, 1001, true

5000, Tx-1002, 2001-11-03T15:38:52.330+01:00, 1002, true

Your output will then build this up again. I have a question about your output because I can see a potential flaw in this structure. In your "data" objects, you have 2 sub-objects in the examples above. So you have 2 identifiant and valide entities in each. Looking at your input data I see that this is likely because your cash and dateAt values are the same. What if you have 3 the same? Would your data object grow to hold 3 sub-objects? If so, this may cause you problems. It might be better to have data as an array of JSON objects holding identifiant and valide.

Anyway, this structure may be difficult to build with a single tWriteJSONField component. They are really meant for simple structures. The subscription Talend product has Talend Data Mapper which would be better suited to this. Alternatively (and a favourite method of mine), you can use Java to build slightly more complicated structures. But the first issue you need to resolve is how you will extract your data from JSON and group it.