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]

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.

[nganapathiraju]

Ignatius_cases_2017_08_04.slp (9.0 KB)

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

[IgnatiusN]

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

[tstack]

IgnatiusN:

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 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.

IgnatiusN:

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

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()).

[IgnatiusN]

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