Lookup on an array in the input document

[IgnatiusN]

Hello,

I have a incoming JSON document that has cases and array of casesteps inside. I need to do a lookup on the case step code from a table in the database and add additional properties to the casestep. Is there a way to accomplish this? We tried doing SQL Lookup or In Memory lookup but joining with each case step on the code didn’t work. The jsonPath syntax we were using always returned an array of codes and the lookup was failing. Any help is appreciated!

[
{
caseId: 1,
caseDate: “2017-08-04”,
caseSteps: [{
code: “xyz”,
value: 10,
primary: null
},
{
code: “abc”,
value: 20,
primary: null
},
{
code: “def”,
value: 20,
primary: null
}]
},
{
caseId: 2,
caseDate: “2017-08-04”,
caseSteps: [{
code: “rew”,
value: 10,
primary: null
},
{
code: “wbc”,
value: 20,
primary: null
},
{
code: “wwe”,
value: 20,
primary: null
}]
}
]

Thanks

[nganapathiraju]

[{, …}, {, …}]
{codeArr:[xyz, abc, def], codeStr:xyz,abc,def}
“codeArr”: [xyz, abc, def]
“xyz”,
“abc”,
“def”
“codeStr”: “xyz,abc,def”
{codeArr:[rew, wbc, wwe], codeStr:rew,wbc,wwe}
“codeArr”: [rew, wbc, wwe]
“rew”,
“wbc”,
“wwe”

“codeStr”: “rew,wbc,wwe”

Is this what you were expecting? of course if you want a single value from the code, you would need to define the criteria.

You can further do all variations

PS: It would be nice if you pasted your entire RAW json. I had to plugin the "" around all json elements and jsonify it to get the exact format.

[IgnatiusN]

Thank you for your response, and sorry for the inconvenience with JSON. My requirement is little different. Given the original JSON source, I have to do a lookup on a second JSON source and add the exp_time property to the case step

[
{
“code”: “abc”,
“exp_time”: “30s”
},
{
“code”: “xyz”,
“exp_time”: “20s”
},
{
“code”: “def”,
“exp_time”: “40s”
}
]

The output we are expecting is something like

[
{
“caseId”: 1,
“caseDate”: “2017-08-04”,
“caseSteps”: [{
“code”: “xyz”,
“value”: 10,
“primary”: null,
“exp_time”: “20s”
},
{
“code”: “abc”,
“value”: 20,
“primary”: null,
“exp_time”: “30s”
},
{
“code”: “def”,
“value”: 20,
“primary”: null,
“exp_time”: “40s”
}]
}
]

[nganapathiraju]

please post the second json source and mention the condition you want to join.

You can add exp_time to caseSteps in the mapper snap.

That was just a sample to manipulate JSON but you can achieve more granularity.

[IgnatiusN]

Thanks Again, the second json source is given in the post. I have it here again. The join is on the “code” from “casestep” to the “code” in this source. exp_time from second source has to be copied over the main document

[
{
“code”: “abc”,
“exp_time”: “30s”
},
{
“code”: “xyz”,
“exp_time”: “20s”
},
{
“code”: “def”,
“exp_time”: “40s”
}
]