Converting Snowflake Lookup results into an array

[jfpelletier]

Hello all,

I'm using the Snowflake Lookup snap to query a table that contains the details of projects already processed, and get the values of the "project ID" column. The values are unique project IDs that I need to store somehow (I'm thinking an array) for another snap to later check if that ID was previously used or not (to determine if it's the first time we deal with that project ID, or if it was already processed and stored in the Snowflake table.

The Snowflake Lookup snap seems to return only one value at the time, but I need to have all the values stored into one structure that I can use later to check the presence of the unique IDs.

The screenshot below shows the output of the Snowflake Lookup. 

When I use a mapper right after, it can convert the "UID" into arrays, but one element per array, and one array per ID...

That's not what I need, I need to have all the IDs in the same structure...

Or... Is it better to query Snowflake for each and every ID that I need to check instead of querying Snowflake one time at the start of the pipeline execution and getting a list to re-use?

Thanks a lot in advance for any help!

Kind regards,

JF

[Abhishek_Soni37]

Hi @jfpelletier 
Please share the sample response format that you are expecting.

[jfpelletier]

Hello @Abhishek_Soni37,

What I have in mind is getting one single array with all the "UID" values in it, something like this: ["UID1", "UID2", "UID3"] instead of one UID returned by array (["UID1"] + ["UID2"] + ["UID3"]).

I think also that my approach might not be correct. I'm simply trying to lookup for the value of a UID and avoid an error output if it's not stored in the DB (the Snowflake lookup snap returns an error when the lookup fails). That's why I'm thinking about putting all the UID values returned in an array, and I can easily find if a value is there without an error message if I use an array method. If there's a better way to get my results I'll be more than happy to hear about it. 🙂

I could also continue to use the Snowflake snap error output as confirmation that a value doesn't exist in the DB, however this would be confusing if I start getting "real" errors from that snap.

Thanks in advance for any help!

Kind regards,

JF

[Abhishek_Soni37 · Accepted solution]

Hi @jfpelletier ,

I did the below to get the UIDs in an array, maybe you fine-tune it a bit more to reduce the snaps:

Input:

[
    {"UID":"UID1"},
    {"UID":"UID2"},
    {"UID":"UID3"},
    {"UID":"UID4"}
]

I used a mapper snap after this and since we want to get all documents in a single array, I added the below expression to extend the UID doc with a common value that I can use later in group by fields snap to group all the records.

$.extend({"group":"1"})

The output of this mapper looks like this:

Later I added a group by fields snap and grouped the input based on the group tag:

The output will be an array of all documents where the group has same value, in our case it'll be the same for all records of UIDs.

Added another mapper with the below expression:

jsonPath($, "group[*].UID")

The final o/p looks like this:

Hope this helps.

Cheers,

Abhishek

[jfpelletier]

Hello @Abhishek_Soni37,

Thanks a lot for your help, it resolved my problem the way I wanted. This is something I will probably re-use a lot, super useful for me! 🙂

Kind regards,

JF