Can we filter a complex structure?

[michelle-popovi]

I have an array of complex data (see screen shot) - I want to find the top level array where an inner level value is matched

In the example below there are 3 main array elements - and in a nested level for Type  there is a value of 'Type1' for 2 of the 3 items in the outer array.

Is there a way to return only the array elements that are of  value 'Type1' in the example below?  Only thing I've found that I can do is create a List (Workato variables) and map the 'value' fields for Type, ID, Status fields...and then filter the list for what I want.  Was hoping to find a way to do this all inside a formula in order to facilitate bulk operations.  I've played around with .where condition in multiple ways but simply haven't been able to figure out what syntax I can use to filter the list so that the whole outer array item is returned.  I can pluck my way down to the inner list...but then I lose the other fields and only have the plucked field...which is not useful..  want to filter on 'Type' and use that to get the value for 'ID'

[gary1 · Accepted solution]

Workato doesn't support the filter method for arrays, which would allow you block process all of the array's items. The easiest alternative to do this (IMO) is to put the payload into a Ruby action and write some code to get what you need. Ruby syntax is quite straightforward, and using the Ruby action is a piece of cake.

The gist of the action is quite simple. First, name the action and then create your input schema to pass in your code:

Then, I prefer to write the code:

## reference your input like this
a = input["data"]

## do some stuff
b = a + a

## declare an object to return as output
{result: b}

Finally (now that you finished your code), create an output schema to match your output object. This will create data pills that could be used in the following steps.

Run it, and you'll get this as output:

Once you have the basics down, this because extremely powerful. Need to do something that requires more complexity than in-line formulas support? Need to loop through a million items (literally) but don't want to spend a million tasks? Do it in one task by using a Ruby action! It's game changing. (You can also do with with JavaScript or Python if that's your cup of tea.)

In the interest of saving you time, here's what you need. Drop this code in a Ruby action and it should do the trick:

## provide the findIdentifiers array as your "data" input and grab it
array = input["data"]

## create an empty array to store your keepers
itemsToKeep = []

## loop through findIdentifiers
array.each { | a | 
  
  ## dig up the type value; this assumes only one object in the Type array 
  type = a["value"]["Type"][0]["value"]
  
  ## test the type value with a ternary operation, 
  ## which is just simplified shorthand for an if statement.
  ## if the type is right, push it to itemsToKeep
  type == "Type1" ? itemsToKeep.push(a) : null
  
}

## create the output object with itemsToKeep as the value
{result: itemsToKeep}

If you remove all of the comments, this is a whopping six lines of code.

To test it, I provided this JSON as input with the goal of only retaining the first and third item in the array (Type1):

{
	"findIdentifier": [{
			"label": "ONE - KEEP",
			"value": {
				"Type": [{
					"type": "blah blah type",
					"ov": true,
					"value": "Type1",
					"uri": "blah blah uri"
				}],
				"ID": [],
				"Status": [],
				"ov": true,
				"uri": "blah uri"
			}
		},
		{
			"label": "TWO - DON'T KEEP",
			"value": {
				"Type": [{
					"type": "blah blah type",
					"ov": true,
					"value": "Type2",
					"uri": "blah blah uri"
				}],
				"ID": [],
				"Status": [],
				"ov": true,
				"uri": "blah uri"
			}
		},
		{
			"label": "THREE - KEEP",
			"value": {
				"Type": [{
					"type": "blah blah type",
					"ov": true,
					"value": "Type1",
					"uri": "blah blah uri"
				}],
				"ID": [],
				"Status": [],
				"ov": true,
				"uri": "blah uri"
			}
		}
	]
}

And here is the result:

Hope this helps!

[Chaiphat]

@gary1 How would you pass in the Json arrays when the input variable for ruby or java action doesn't supports array or list type?