question Re: Extracting List(Array) element using JOLT in Support Questions

Extracting List(Array) element using JOLT

saquibsk — Sun, 21 Apr 2024 20:45:55 GMT

Hello,

Can you assist me in retrieving the values stored in the "customfield_10161" field from JSON input? My expectation is to consistently receive the values and name it "DEFECT_ROOT_CAUSE" , whether they are stored as a list or as a single value."

How to write JOLT which will handle the both the case. let say If I will get list then extract last element of it, if not list then record as it is.?

Extracting List: (works fine when input is list)

when input is not list:

then DEFECT_ROOT_CAUSE column is missing My expectation to get the records in DEFECT_ROOT_CAUSE columns whether its list or not.

Re: Extracting List(Array) element using JOLT

SAMSAL — Sun, 21 Apr 2024 22:36:34 GMT

Hi @saquibsk ,

The below spec should put you on the right path if it doesnt completely solve your issue:

[ // Assign isArray flag field when there is an array (index starts at 0) { "operation": "shift", "spec": { "id": "INTEGRATION_ID", "*": { "*_*": { "@": "&(1,1)", "0": { "#true": "isArray" } } } } } , //If the input is not an array assign default isArray=false { "operation": "default", "spec": { "isArray": "false" } } , //Depending on with isArray true of false: //Transpose values into DEFECT_ROOT_CAUSE_List array { "operation": "shift", "spec": { "customfield": null, "*": "&", "isArray": { "true": { "@(2,customfield)": { "*": { "value": { "@": "DEFECT_ROOT_CAUSE_List[]" } } } }, "false": { "@(2,customfield)": "DEFECT_ROOT_CAUSE_List[]" } } } } , // Finally, get the last element of the array { "operation": "modify-overwrite-beta", "spec": { "DEFECT_ROOT_CAUSE": "=lastElement(@(1,DEFECT_ROOT_CAUSE_List))" } } ]

If that helps please accept solution.

Thanks

Re: Extracting List(Array) element using JOLT

saquibsk — Mon, 22 Apr 2024 05:17:53 GMT

Hi @SAMSAL,

Thank you for the reply.

There are still no columns showing in the non-list scenario.

Re: Extracting List(Array) element using JOLT

SAMSAL — Mon, 22 Apr 2024 12:47:23 GMT

In my jolt spec its expecting customfield_10616 , so in my spec if you notice in the first shift Im using the expression *_* basically to make more dynamic but at least you have to have an underscore but if you think otherwise you can change it to whatever works for your data, for example you can change it to "customfield" , "customfield_*" , "*" , etc.

Re: Extracting List(Array) element using JOLT

saquibsk — Tue, 23 Apr 2024 06:33:12 GMT

This customfield_ will not change because it is static. I'm handling this aspect in NiFi right now, however I get into trouble when I acquire more fields as lists and have to change the NIFI Flow.

Re: Extracting List(Array) element using JOLT

saquibsk — Tue, 23 Apr 2024 10:58:52 GMT

This is how I am currently handling the records. All I'm doing is flagging the rows and sending them to the appropriate process. However, suppose in the future that a new field or an existing one begins to capture List data; in that case, I would need to modify the flow and add more conditions. I just needed a single field, therefore let's say that if it captures the list, it will show up as the last record or as a single record.

Re: Extracting List(Array) element using JOLT

SAMSAL — Tue, 23 Apr 2024 13:40:40 GMT

I think the issue here is stating what the problem is vs the actual problem. since you starting posting the issue, I noticed the json input has changed 3 times. Remember as someone said "Asking the right question is half way the path to finding the right solution".

Allow me give you some pointers:

1- Please specify the different json input for all possible scenarios and the expected output\s.

2- If you have compound problem then divide and conqueror. Break the big problem into smaller one where each is isolated to its own input, output and assertions..

3- Simplify if you can, for example if you have complex json input and you are only facing issue with particular field or nested object then you dont have to post the whole json , instead you isolate where the issue is and provide that part only or create new one that mimic the same structure.

4- If you have code or some formatted data please use the code block "</>" from the menu item for better visibility and readability. Dont post code\data as screenshot.

5- Use screenshots for when suitable , like showing nifi flow, processor configuration ..etc.

6- Refer to the community guidelines for more info: https://community.cloudera.com/t5/custom/page/page-id/Community_Guidelines

Hope that helps.

Re: Extracting List(Array) element using JOLT

saquibsk — Tue, 23 Apr 2024 17:01:50 GMT

Hi @SAMSAL ,

My requirement is very simple.

I anticipate receiving the single records in the output. That's DEFECT_ROOT_CAUSE, regardless of whether my input is a list or sinlge records.

(The primary source is JIRA; a bug ticket has been generated there, indicating that the issue may be in the front end or back end. However, occasionally a reporter will tag the back end before the front end, leading to the creation of a list. )

> my input is almost 200 line however I am only putting the required column only. I have posted two image one is for list records and one is single records with same column (only records is getting changes that's it.

Re: Extracting List(Array) element using JOLT

SAMSAL — Tue, 23 Apr 2024 22:59:27 GMT

Based on my json spec above:

1- customfield_* with multiple records list

2- customfield_* with single records list:

3- customfield_* with no list:

The asterisk in customfield_* means anything as long there is a customfield , underscore (_) and whatever comes after.

Re: Extracting List(Array) element using JOLT

saquibsk — Mon, 06 May 2024 12:12:26 GMT

Hi @SAMSAL ,

Am I missing something on the JOLT?

Not getting output as expected. (DEFECT_ROOT_CUASE) columns is missing from output.

=========================================== --Input =========================================== { "id": "33414", "fields": { "customfield_10161": [ { "value": "Backend" }, { "value": "Frontend" } ] } } =========================================== --JOLT Spec =========================================== [ // Entries (where "flat" "data" when there is an array) (now starts at 8) { "operation": "shift", "spec": { "id": "INTEGRATION_ID", "*": { "*_*": { "@": "&(1,1)", "0": { "#true": "isArray" } } } } }, //If the input is not an array, assign default isArray=false { "operation": "default", "spec": { "isArray": "false" } }, //Depending on with isArray true for false //Transpose values into DEFECT_ROOT_CAUSE_List_Array { "operation": "shift", "spec": { "customfield": null, "*": "&", "isArray": { "true": { "@(2,customfield)": { "*": { "value": { "@": "DEFECT_ROOT_CAUSE_LIST[]" } } } }, "false": { "@(2,customfield)": "DEFECT_ROOT_CAUSE_LIST[]" } } } }, // Finally, get the last element of the array { "operation": "modify-overwrite-beta", "spec": { "DEFECT_ROOT_CAUSE": "=lastElement(@(1,DIRECT_ROOT_CAUSE_LIST))" } } ] =========================================== --Output =========================================== { "INTEGRATION_ID" : "33414", "DEFECT_ROOT_CAUSE_LIST" : [ "Backend", "Frontend" ] }

Re: Extracting List(Array) element using JOLT

SAMSAL — Mon, 06 May 2024 14:05:31 GMT

The reason its not working because there is mismatch in the list name 3rd spec where its using "DEFECT_ROOT_CAUSE_LIST" and the 4th spec where its using "DIRECT_ROOT_CAUSE_LIST" . If you fix this it should work.

If you find this is helpful please accept solution.

Thanks

Re: Extracting List(Array) element using JOLT

saquibsk — Mon, 06 May 2024 20:42:17 GMT

Thank you very much, @SAMSAL. I really appreciate your time and effort.

My input format has changed. I will adjust the JOLT accordingly if it does not work or encounter any issues, I will ask one final question. Otherwise, I will mark your last reply as the solution.

Re: Extracting List(Array) element using JOLT

saquibsk — Mon, 06 May 2024 20:54:19 GMT

Just to give a heads up, my input will be something like the example below. I have around 60 columns, and out of those, only 4 columns are in the list.

Example: from below example i would like to handle the "DEFECT_ROOT_CAUSE" and "SPRINT_LIST"

Output would be same as your solution:

DEFECT_ROOT_CAUSE:"Frontend"

SPRINT_LIST:"24-w2-3"

================================== -- input 1 ================================== [ { "INTEGRATION_ID" : "1111", "ISSUE_KEY" : "PP2-7426", "SUMMARY" : "Confirmation Web", "SPRINT_LIST" :"24-w2-1", "DEFECT_TYPE" : "poor user interface design, confusing navigation", "TEAM" : "Web Engineering", "PLATFORM" : "Customer Web", "DEFECT_ROOT_CAUSE" : [ "Backend", "Frontend" ] } ] ================================== -- input 2 ================================== [ { "INTEGRATION_ID" : "2222", "ISSUE_KEY" : "PP2-7427", "SUMMARY" : "Confirmation Web", "SPRINT_LIST" : [ "24-w2-1", "24-w2-2", "24-w2-3" ], "DEFECT_TYPE" : "poor user interface design, confusing navigation", "TEAM" : "Web Engineering", "PLATFORM" : "Customer Web", "DEFECT_ROOT_CAUSE" : "Backend" } ]

Re: Extracting List(Array) element using JOLT

SAMSAL — Mon, 06 May 2024 21:21:38 GMT

Based on the provided new input and the expected output as I was able to understand where you always want to consider the last element of both DEFECT_ROOT_CAUSE & SPRINT_LIST which both may or may not be an array of values, I would take a different approach to the jolt spec which is much simpler as follows:

[ // First spec is convert everything into list regardless { "operation": "cardinality", "spec": { "*": { "SPRINT_LIST": "MANY", "DEFECT_ROOT_CAUSE": "MANY" } } } , // From the generated list above return last element { "operation": "modify-overwrite-beta", "spec": { "*": { "SPRINT_LIST": "=lastElement", "DEFECT_ROOT_CAUSE": "=lastElement" } } } ]

Re: Extracting List(Array) element using JOLT

saquibsk — Tue, 07 May 2024 06:43:33 GMT

Thank you, @SAMSAL. This logic is much simpler. My JOLT logic was a bit complex. This is going to save me a lot of time.

Is there are any tutorial where we can learn the JOLT ?

Re: Extracting List(Array) element using JOLT

SAMSAL — Wed, 08 May 2024 05:29:19 GMT

There is not really a comprehensive Jolt tutorial that I could find out there. Two important references I frequently use to learn is :

1- The Jolt code in github where you can see how things work under the hood and what are the available functions:

https://github.com/bazaarvoice/jolt

2- A site that talks about the fundamental of jolt:

https://docs.digibee.com/documentation/components/tools/transformer-jolt/transformer-getting-to-know-jolt#cardinality

The rest is practice, practice and more practice. Its the only way you can get some depth in the understanding of jolt and its capabilities. You can do that by trying to help and learn from others posts in this community forum or other forums like the below where you can find tons of jolt problems\solutions:

https://stackoverflow.com/questions/tagged/jolt

Hope that helps and thanks for accepting the solution.

Re: Extracting List(Array) element using JOLT

omeraran — Wed, 05 Jun 2024 07:31:35 GMT

hi SAMSAL, i send you message. Can you check it please ?