This article is a guide for extracting key-pair values from a JSON payload with multiple objects in Workflows.
- Workflows
- Extract key-pair values
- Setting up the JSON file.
{
"sheets": [
{
"properties": {
"sheetId": 0,
"title": "Sheet1",
"index": 0,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
},
{
"properties": {
"sheetId": 317732887,
"title": "Sheet2",
"index": 1,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
},
{
"properties": {
"sheetId": 2125125274,
"title": "Sheet3",
"index": 2,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
},
{
"properties": {
"sheetId": 144976084,
"title": "Sheet4",
"index": 3,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
},
{
"properties": {
"sheetId": 1179906118,
"title": "Sheet5",
"index": 4,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
},
{
"properties": {
"sheetId": 1615383701,
"title": "Sheet6",
"index": 5,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
},
{
"properties": {
"sheetId": 561243345,
"title": "Sheet7",
"index": 6,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
}
]
}- This JSON can be obtained from an API call. Use the Mock API service to create an endpoint that returns the above JSON for testing.
Click on this link for an example of an API endpoint.
- If API use is not an option, the Compose and Parse cards can be used to create a list of JSON objects shown in the screenshot below. Using the Compose and Parse cards to create a list of JSON objects.
- Since we are working with a list, the solution has two flows:
- A main flow to get a list of objects (JSON file).
In this flow:
- The API Connector – Get card calls the API endpoint to get the JSON data.
- In the cards Response section, the path sheets is added to the Body field. This allows direct passing the sheets field to the next card.
- The sheets field is a JSON array, and its type is a list of objects.
- The sheets field (JSON array) is passed to the List – For Each card.
- The Get Multiple Keys_Helper helper flow is called for each item in the list (passed in the With the following values section).
- A helper flow that processes each item in a list and retrieves the key-pair value(s).
This is the helper flow:
The first card, the Helper Flow, receives each item (JSON object) that looks like this:
{
"properties": {
"sheetId": 561243345,
"title": "Sheet7",
"index": 6,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
}
- Using the Get Multiple card.
Next, specify the key for retrieving the value from the JSON object using the Get Multiple card. Use the dot notation for nested objects.
The keys and their types specified on the card are:
- properties (type Object)
- properties.title (type Text)
- properties.gridProperties (type Object)
- properties.gridProperties.rowCount (type Text)
Output from the helper flow run:
The properties JSON key is used in every get. The properties key on Helper Flow card can be defined, without the need to use the name in the Get Multiple card.
The updated helper flow with the properties key set:
In the screenshot above, the properties key (type Object) is set on the Record field on the Helper Flow card. This allows passing the properties key value to subsequent cards.
The keys and their types specified on the card are:
- properties.title (type Text)
- properties.gridProperties (type Object)
- properties.gridProperties.rowCount (type Text)
