Evaluating an array value that's nested in a Group By Field and Routing the Group By Level based on Array value

Question

I have created a Group By Field on an Order Number.  Underneath that order number I have an array of AI_Status values.  I would like to assess the entire array if any of the AI_Status values with the array are outside of the following array values [8, 16,32] and if so I would like to route that order number and the underneath array down one path and send the Order Number who AI_Statuses are either an 8,16, or 32 down another path.
Below is an example of the JSON as it exits the Group By Field Snap:
[
{
“groupBy”: {
“ContextName”: “202206871SRCN”
},
“processes”: [
{
“Name”: “AU-I6”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.487”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.000”
},
“AI_Status”: 70,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206871SRCN”
},
{
“Name”: “AU-I50”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.530”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206871SRCN”
},
{
“Name”: “AU-CORP1Buy”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.540”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206871SRCN”
},
{
“Name”: “AU-UCCFinP”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.557”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206871SRCN”
},
{
“Name”: “AU-S11”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.570”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206871SRCN”
},
{
“Name”: “AU-T1a AU”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.590”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206871SRCN”
},
{
“Name”: “AU-CORP1Sel”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.610”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206871SRCN”
},
{
“Name”: “AU-X13”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.627”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:28:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206871SRCN”
}
]
},
{
“groupBy”: {
“ContextName”: “202206896SRCN”
},
“processes”: [
{
“Name”: “AU-I6”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.487”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206896SRCN”
},
{
“Name”: “AU-I50”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.530”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206896SRCN”
},
{
“Name”: “AU-CORP1Buy”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.540”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206896SRCN”
},
{
“Name”: “AU-UCCFinP”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.557”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206896SRCN”
},
{
“Name”: “AU-S11”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.570”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206896SRCN”
},
{
“Name”: “AU-T1a AU”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.590”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206896SRCN”
},
{
“Name”: “AU-CORP1Sel”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.610”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206896SRCN”
},
{
“Name”: “AU-X13”,
“Enabled”: true,
“AI_CreatedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.627”
},
“AI_QueuedOn”: {
“_snaptype_localdatetime”: “2022-11-07T22:27:05.000”
},
“AI_Status”: 8,
“Ai_Reason”: “Completed successfully.”,
“ContextName”: “202206896SRCN”
}
]
}
]

bojanvelevski · Answer

Hello @bcole,
I included a pipeline in the post that evaluates the array by using the array filter function. If the array objects, are filtered out based on the AI_Status field (their value is 8,16 or 32), and the array is not empty, then that means one of the AI_Statuses has some other value.
I named that additional field as routingPath which can be easily used for further processing, in your case routing.
Routing_FieldValue_2022_11_10.slp (11.7 KB)
Hope this helps,
Bojan

bcole · Answer

Bojan,
I think that solutions works nicely. Thank you so much. I just have one other question. What is the best way to add a value, specifically the IpaasQueueId , and EntityKey to Rows that are further down stream like after the stored procedure snap. The first SQL Snap and the second snap have an EntityKey = ContextName relationship. I need that and the IpaasQueueId from the first SQL call.

bojanvelevski · Answer

I’m not sure I understand @bcole, can you elaborate a bit more?

bcole · Answer

Hi Bojan,
I think I figured it out. Perhaps the diagram below can show you what I was asking. I wanted to pull data from one early snap and get that information back way further down the pipeline.
I needed to join some of my original data from the first SQL Server JSON response with Data from my SQL Store Procedure call: I was asking for the preferred way of making that happen. Below is what I came up with. Unless you have a better idea, I think we can consider the case closed.
The expression you sent was great, but how would I know to use the “match” with the filtering. I have taken your Expression training class, but I would like to know another resource that can help me learn to create all the complicated Expressions I will need going forward. Also I tried using jsonPath to reach a solution. Some of the jsonPath would work on online validators, but would not inside various snaps.
Where do you point people to learn the brand of jsonPath that works here?

Forum Discussion

Evaluating an array value that's nested in a Group By Field and Routing the Group By Level based on Array value

4 Replies

Recent Discussions

Google Sheets Subscribe questions

Basic string transformations not working

Can we generate XML file in pretty print format using native snapLogic snaps?

Multipart Reader failure - 'content-type' was not found

Unable to write sldb file to file system