โ06-09-2020 02:36 AM
Hello all,
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!
Cheers.
Solved! Go to Solution.
โ06-10-2020 12:45 AM
Hi @khenderson,
I believe your problem would be solved if you add a Sort Snap before the Group By Fields Snap, and you sort by RevisionID. This is from the documentation for the Group By Fields Snap:
โThe Snap expects the input documents with the same group-by field values are contiguous and whenever the group-by field values changes it produces a new output document. Therefore, if all input documents with the same group-by field values are expected to be grouped into one output document, the Sort Snap can be used in front of the Group By Fields Snap so that the input document stream are sorted by the group-by field values.โ
It can be found at this location: https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1439014/Group+By+Fields
BR,
Dimitri
โ06-10-2020 12:45 AM
Hi @khenderson,
I believe your problem would be solved if you add a Sort Snap before the Group By Fields Snap, and you sort by RevisionID. This is from the documentation for the Group By Fields Snap:
โThe Snap expects the input documents with the same group-by field values are contiguous and whenever the group-by field values changes it produces a new output document. Therefore, if all input documents with the same group-by field values are expected to be grouped into one output document, the Sort Snap can be used in front of the Group By Fields Snap so that the input document stream are sorted by the group-by field values.โ
It can be found at this location: https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1439014/Group+By+Fields
BR,
Dimitri
โ06-10-2020 01:24 AM
Hi Dimitri,
Thanks a lot for your reply.
Clearly I wasnโt reading the docs closely enough!
Iโll give it a go and let you know.
Thanks,
Kyle
โ06-10-2020 01:28 AM
That solved everything.
Thanks again!