Forum Discussion

IgnatiusN's avatar
IgnatiusN
New Contributor II
8 years ago

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

10 Replies

  • nganapathiraju's avatar
    nganapathiraju
    Former Employee
    8 years ago

    [{, …}, {, …}]
    {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's avatar
    IgnatiusN
    New Contributor II
    8 years ago

    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's avatar
      nganapathiraju
      Former Employee
      8 years ago

      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's avatar
    IgnatiusN
    New Contributor II
    8 years ago

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

    • nganapathiraju's avatar
      nganapathiraju
      Former Employee
      8 years ago

      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.

    • tstack's avatar
      tstack
      Former Employee
      8 years ago

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

      • IgnatiusN's avatar
        IgnatiusN
        New Contributor II
        8 years ago

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