topic Re: Convert JSON Object to list of Key/Value pairs in Talend Studio

Convert JSON Object to list of Key/Value pairs

ksudm — Fri, 15 Nov 2024 22:18:39 GMT

I'm invoking the AdobeSign REST API and retrieving a list of fields on a given agreement.

I'm having troubles in trying to pivot this response to a list of KV pairs (which will later be stored in a DB table with a schema like: agreementId|key|value)

A sample response from the API migth look like this:

>> {"Department":"IT","role":"SIGNER","last":"Doe","Username":"jdoe","agreementId":"CYJCHBEWABAA39c6RGZvWjU_THltN_TfFGKBM2swbyD2","company":"CompanyA.","USER ID":"8100012345","completed":"2022-11-21 07:59:18","title":"","email":"jdoe@email.com","first":"John"}

The catch, is that each API call might generate a different list of fields, so defining a fixed Schema will not work.

I've thought about using a tMap - but I'm not sure how to setup the expression to have it split on the JSON object's keys.

I've also considered using a tJavaRow to parse the JSON object in a loop (easy enough) - but I'm missing how to convert the output to a list in tJavaRow.

psuedo-code for tJavaRow:

foreach(string key in JSON.parse(input_row.fields))

{

output_row.key = key;

output_row.value = input_row.fields.key

output_row.agreementId =

(String)globalMap.get("AgreementId");

}

any help or guidance on handling non-fixed schemas would be helpful.

Re: Convert JSON Object to list of Key/Value pairs

Anonymous — Thu, 01 Dec 2022 16:49:42 GMT

You should use the tExtractJSONFields component for this. An API will always return only the fields specified in the API documentation. It won't necessarily return each of them each time, but it won't add more unless the API is changed. As such, you should set up your tExtractJSONFields component to attempt to collect every possible field that could be returned. After you have that, you can use logic within the job to get hold of the data you need.

Re: Convert JSON Object to list of Key/Value pairs

ksudm — Fri, 02 Dec 2022 18:13:07 GMT

Thanks for the reply - I left out the parent node in this JSON response (thinking it wasn't relevant).

The full JSON response from tRESTclient looks like this:

{"formDataList":{"fieldA": "foo", "fieldB":"bar" .... }}

and another call (using a different agreementId to the API)

{"formDataList":{"fieldC": "morefoo", "fieldD":"morebar" .... }}

The items under formDataList are unique to each invocation of the API. The only schema element that will be expected will be "formDataList" at the root.

Re: Convert JSON Object to list of Key/Value pairs

ksudm — Fri, 02 Dec 2022 18:14:30 GMT

FYI - I'm going to test your post on another thread to see if a tJavaFlex component might help

https://community.talend.com/s/question/0D55b000086tBowCAE/how-to-process-anonymous-json-for-key-value-pairs

Re: Convert JSON Object to list of Key/Value pairs

Anonymous — Fri, 02 Dec 2022 18:24:10 GMT

OK. I may have misunderstood your request a little here. The other solution I provided may work for you. It does seem a bit strange that an API would return fields which are not pre-identified. But hey, it is possible I guess. Just not ideal.

Re: Convert JSON Object to list of Key/Value pairs

ksudm — Fri, 02 Dec 2022 19:03:07 GMT

Thanks it looks like that did the trick.

For context, this API returns back custom attributes from an AdobeSign agreement. Each agreement (of type of agreements) will have their own form fields...hence why these would be different.

The top row is a check for a BearerToken needed for tRestClient.

After tRESTClient, tExtractJSONFields_1 pulls out "formDataList" and stashes that string into a globalMap var (the data is a single string...otherwise might use tBufferOutput?).

I found that if I didn't put tJavaFlex in its own subJob, the JSON results from tRestClient wouldn't be available to the Start of tJavaFlex (same applies to tJava).

Then tJavaFlex can start off with:

`JSONObject obj = new JSONObject((String)globalMap.get("jsonFields"));`

Re: Convert JSON Object to list of Key/Value pairs

Anonymous — Fri, 02 Dec 2022 19:19:20 GMT

Glad it works! This one of the key benefits of Talend that brought me to spend a big chunk of my career working with it and now working for Talend. The ability to enhance what comes out of the box with a bit of Java, is SO powerful.