Lookup on an array in the input document

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

[{, …}, {, …}]
{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.

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”
}]
}
]

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.

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”
}
]

I hope this is what you want.

But without complete requirement and data, I cannot get you complete solution.

Definitely this should be a way forward for you.

Ignatius_cases_2017_08_04.slp (9.0 KB)

Attached is the sample I worked. I am sure you can improve upon.

The Lookup snaps are going to operate on documents and not arrays inside of documents. You could use a JSONSplitter snap to break up the array into multiple documents, but then you probably need to collect them back together to keep the original format, which can be difficult. It might be easier to use the expression language to perform the lookups.

Would you be able to restructure this source data so that it is more like an object? For example:

{
  "abc" : {
    "exp_time": "30s"
  },
  "xyz" : {
    "exp_time": "20s"
  },
  ...
}

If so, the lookup is then just a matter of getting the property from this object. If this JSON source data is static and in a file, you can import it via an expression library (see https://doc.snaplogic.com/wiki/spaces/SD/pages/1438110/Expression+Libraries).

For example, if you imported this source data as a library with the name ‘source’. You can then use a map() method to perform the transform on the array:

$.caseSteps.map(elem => elem.extend(lib.source[elem.code]))

This code will iterate over the the elements of the array using the map() method. For each element, it will execute the function that was passed in to create the new value for the element. The callback function looks up the data to add in to the object (lib.source[elem.code]) and then creates a new object from the existing one and the extra data (elem.extend()).

Thank you, the example is very helpful. Appreciate it!

Thank you we will look into this. Is it possible to load the expression library from database?

No, unfortunately, the library has to come from a URL at the moment.