Forum Discussion

jfpelletier's avatar
jfpelletier
Contributor
2 years ago
Solved

Converting Snowflake Lookup results into an array

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 ...
  • Abhishek_Soni37's avatar
    Abhishek_Soni37
    2 years ago

    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's avatar
jfpelletier
Contributor
2 years ago
Copy LinkReport

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's avatar
    Abhishek_Soni37
    Contributor
    2 years ago
    Copy LinkReport

    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's avatar
      jfpelletier
      Contributor
      2 years ago
      Copy LinkReport

      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