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

[bcole]

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]

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]

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]

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

[bcole]

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?