Group By Fields After Mapper / Structure

[khenderson]

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.

[dimitri_hristov · Accepted solution]

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

[khenderson]

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

[khenderson]

That solved everything.

Thanks again!