I’m currently creating a pipeline which some basic distribution data for a document. Each of these has a foreign key ‘RevisionID’.
I need to do multiple lookups on different tables to pull through and replace other foreign keys with the data that should be there.
To achieve this what I’ve been attempting is to do is do a lookup, use a mapper and a structure to flatten the Json down. At the end I’m using a ‘group by fields’ snap to try group these into array of entries based on the ‘RevisionID’.
The issue that I am having is that if I do the ‘group by fields’ immediately after the initial SQL SELECT (where I pull info from the distribution table) it seems to work as expected, however, after doing the lookups it no longer correctly groups.
Doing the group by fields first means that I get an error saying an SQL Lookup snap cannot accept a ‘List’ of values. I tried using jsonPath to iterate through these but I’m still learning jsonPath so maybe I’m missing something there.
I’ve included screenshots of the data that does group by field correctly and the data that does not group by field correctly. To me they look similar in structure so I’m not sure what is going on.
Is there something I’m missing?
This is the data straight from the table, group by fields works fine when attached directly to this output:
This shows what happens with group by fields if applied before transformation:
This is the data after I’ve pulled the names and flattened the json structure back:
This shows what happens with group by fields if applied after transformation using mapper/structure:
I’m not sure what is causing this but relatively new to SnapLogic so aware there may be something I am missing.
If anyone can provide some insight into what is going on or if they need more info it’d be much appreciated!