Flow Group By

Ants · ‎07-17-2018

I was playing around with Flow and needed some way to group a collection of rows by a specific column. The articles online looked too complicated for something so simple, so I thought this would be a good place to get my hands dirty.

1. Add a "Request" trigger action which will allow the group by to be called by another Flow:

Add the following to the "Request JSON Body schema":

{
    "type": "object",
    "properties": {
        "key": {
            "type": "string"
        },
        "rows": {
            "type": "array",
            "items": {
                "type": "object"
            }
        }
    }
}

The "key" is the column in a table that you want to group by.
The "rows" are all the columns in the table that you want to be grouped by the key, including the key.

Note: The key data type is of type string therefore the rows whose column is the matching key also needs to be of type string.

2. Add a "Select" variable called "SelectGroupKeys". The From should contain the "rows" from the "Request" step and the map should be switched to text with the following expression:

item()?[triggerBody()?['key']]

This step is going through all the possible values of the column key for each row and storing it as an array.

3. Add an "Initialize variable" with the the name "Initialize Distinct Keys". The variable name should be "varDistinctKeys" of type Array and the value should have an expression of:

intersection(body('SelectGroupByKeys'),body('SelectGroupByKeys'))

This is simply taking all the key values and making it distinct like in SQL:

4. Add another "Initialize variable" called "Initialize Results Array" with the Name "varResults" of type Array, leaving the "Value" empty.

5. Add the "Apply to each" (found under More). In the "Select an output from previous steps", add "varDistinctKeys" from step 3 above. Add a Filter array and in the "From", choose rows from the "HTTP" request.

Set the left "Choose a value" to use an expression of:

item()[triggerBody()?['key']]

The right "Choose a value" is then set to "Current item"

This step is filtering all the rows from the "HTTP" request by the "key".

6. Add an "Append to array variable" step under the "Filter array":

To count the number of items for each key, I added this expression for the count:

length(body('Filter_array'))

The key contains the variable of the current item and the "Group" contains the body from the filter array (which already contains the grouping per key).

The result of the "For each" should look like the following:

7. Finally, add a response to send the result back to the calling Flow:

The "Response Body JSON Schema" contains the following:

{
    "type": "object",
    "properties": {
        "results": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "count": {
                        "type": "number"
                    },
                    "Name": {
                        "type": "string"
                    },
                    "Group": {
                        "type": "string"
                    }
                },
                "required": [
                    "count",
                    "Name",
                    "Group"
                ]
            }
        }
    }
}

To test the new flow

Create a new Flow using a flow button without any inputs. Then add a compose (Found under "Data Operations") with the following:

{
	"key" : "Status",
	"rows": [
			{"Name" : "Flow1", "Status": "Test Succeeded", "Run history" : "17 minutes ago" },
			{"Name" : "Flow2", "Status": "Test Succeeded", "Run history" : "59 minutes ago" },
			{"Name" : "Flow3", "Status": "Test Failed", "Run history" : "3 weeks ago" },
			{"Name" : "Flow4", "Status": "Test Failed", "Run history" : "4 weeks ago" }
		]
}

This example is generates the body of the request where I want all the rows grouped by the "Status".

Then add an HTTP step to call the Group By Flow using the "Method" of "POST" and the "Body" of the Compose "Output":

Give the flow a name, save and run a Test. You should now check the run history, scroll to the HTTP step and review the output, which now contains the following result:

[
    {
        "count": 2,
        "key": "Test Succeeded",
        "Items": [
            {
                "Name": "Flow1",
                "Status": "Test Succeeded",
                "Run history": "17 minutes ago"
            },
            {
                "Name": "Flow2",
                "Status": "Test Succeeded",
                "Run history": "59 minutes ago"
            }
        ]
    },
    {
        "count": 2,
        "key": "Test Failed",
        "Items": [
            {
                "Name": "Flow3",
                "Status": "Test Failed",
                "Run history": "3 weeks ago"
            },
            {
                "Name": "Flow4",
                "Status": "Test Failed",
                "Run history": "4 weeks ago"
            }
        ]
    }
]