query

Task Purpose

The query task is used to extract data out of object variables and then reshape that data for an existing task.

Potential Use Cases

If you have data that does not fit a recognized parameter in an existing task, the query task can reshape that data so that it fits. Using an existing task and then extracting data with a query is more efficient than writing a new task to fit the data. You can also use this task to extract data from a variable for evaluation purposes.

There are also unique situations with the pass_on_null option that can occur when a user performs a query against an object where the query fails.

Properties

Input and output properties are shown below.

Public API Query

In this example, a simple automation was built to obtain data from a remote system using a public API.

On the workflow canvas, a restCall task is set to retrieve numbers information from an available API on the Web. The query task represents the data to search, which will be provided by the results of the restCall task.

Double-clicking the restCall task opens the task dialog and shows the configuration variables (Figure 1). Of note, the URI points to the API: http://numbersapi.com/random/trivia.

Figure 1: Workflow and Rest Call Task Setup

Next, double-clicking the Query task opens the task dialog to show the required input variables (Figure 2). The query parameter is set to search the body of the JSON in the available API, and the Reference task for the obj variable is the response that returns from the REST Call task.

Figure 2: Query Task Dialog

Once the automation is executed (Figure 3), the return_data variable displays a piece of trivia about the number 360 that was randomly generated by querying the publicly available Numbers API.

Figure 3: Task Output

Query Expressions Against an Object

The following examples illustrate various query expressions against an example object exampleObj on an IOS platform.

Example Object

exampleObj = {
    "platform": {
        "ned": "cisco-ios",
        "description": "Cisco IOS Router or Switch"
    },
    "POPs": {
        "ATL": {
            "COUNTRY": "US",
            "ST": "GEORGIA",
            "LN": "ATLANTA",
            "cisco-ios-xr": [
                {
                    "name": "cr1.atl",
                    "type": "router",
                    "make": "Cisco",
                    "model": "ASR9K"
                }
            ],
            "cisco-ios": [
                {
                    "name": "er1.atl",
                    "type": "router",
                    "make": "Cisco",
                    "model": "3945"
                },
                {
                    "name": "sw1",
                    "type": "switch",
                    "make": "Cisco",
                    "model": "Catalyt 2960G"
                }
            ]
        },
        "ORF": {
            "COUNTRY": "US",
            "ST": "VIRGINIA",
            "LN": "NORFOLK",
            "cisco-ios": [
                {
                    "name": "cr1.orf",
                    "type": "router",
                    "make": "Cisco",
                    "model": "7600"
                }
            ],
            "juniper-junos": [
                {
                    "name": "er1.orf",
                    "type": "router",
                    "make": "Juniper",
                    "model": "MX10"
                }
            ]
        }
    }
}

When a query has no matching data and pass_on_null is true, the workflow will complete successfully.

When a query has no matching data and pass_on_null is false, the workflow will return an error in the job history. The query will terminate in a failure.

Query 1

Extract the points of presence with a query that returns the value for the key POPs.

{
    pass_on_null: true,
    query: "POPs",
    obj: exampleObj
}

Query 1 Result

{
    "ATL": {
        "COUNTRY": "US",
        "ST": "GEORGIA",
        "LN": "ATLANTA",
        "cisco-ios-xr": [
            {
                "name": "cr1.atl",
                "type": "router",
                "make": "Cisco",
                "model": "ASR9K"
            }
        ],
        "cisco-ios": [
            {
                "name": "er1.atl",
                "type": "router",
                "make": "Cisco",
                "model": "3945"
            },
            {
                "name": "sw1",
                "type": "switch",
                "make": "Cisco",
                "model": "Catalyt 2960G"
            }
        ]
    },
    "ORF": {
        "COUNTRY": "US",
        "ST": "VIRGINIA",
        "LN": "NORFOLK",
        "cisco-ios": [
            {
                "name": "cr1.orf",
                "type": "router",
                "make": "Cisco",
                "model": "7600"
            }
        ],
        "juniper-junos": [
            {
                "name": "er1.orf",
                "type": "router",
                "make": "Juniper",
                "model": "MX10"
            }
        ]
    }
}

Query 2

Refine the previous example and access an object's property. Use dot notation to access the key's value.

{
  pass_on_null: true,
  query: "POPs.ATL.cisco-ios",
  obj: exampleObj
}

Query 2 Result

[
    {
        "name": "er1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "3945"
    },
    {
        "name": "sw1",
        "type": "switch",
        "make": "Cisco",
        "model": "Catalyt 2960G"
    }
]

Query 3

Refine the previous example. Apply a filter using key=value syntax that only matches objects where make is "Cisco".

{
  pass_on_null: true,
  query: "POPs.ATL.cisco-ios[make=Cisco]",
  obj: exampleObj
}

Notice only the first matched element is returned.

Query 3 Result

{
    "name": "er1.atl",
    "type": "router",
    "make": "Cisco",
    "model": "3945"
}

Query 4

Refine the previous example. Apply a filter to match all objects where make is "Cisco" with the asterisk operator.

{
  pass_on_null: true,
  query: "POPs.ATL.cisco-ios[*make=Cisco]",
  obj: exampleObj
}

Query 4 Result

[
    {
        "name": "er1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "3945"
    },
    {
        "name": "sw1",
        "type": "switch",
        "make": "Cisco",
        "model": "Catalyt 2960G"
    }
]

Query 5

Refine the previous example to return all IOS routers in ATL.

{
  pass_on_null: true,
  query: "POPs.ATL.cisco-ios[*type=router]",
  obj: exampleObj
}

Since the asterisk operator is to match all, an array is returned even if one or no elements match.

Query 5 Result

[
    {
        "name": "er1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "3945"
    }
]

Query 6

Perform a compound boolean query with a regular expression.

In this example, we want all IOS devices in ATL with make Cisco and a name that ends in "atl". The ampersand operator is introduced to perform a boolean and evaluation; both conditions must be satisfied. The tilde (~) operator is also introduced to perform a regular expression match.

{
  pass_on_null: true,
  query: "POPs.ATL.cisco-ios[* make=Cisco & name~/atl$/]",
  obj: exampleObj
}

Query 6 Result

[
    {
        "name": "er1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "3945"
    }
]

Query 7

Perform a deep query. Search through multiple levels of objects or arrays with [**]. Search for any ATL router.

{
  pass_on_null: true,
  query: "POPs.ATL[**][*type=router]",
  obj: exampleObj
}

Query 7 Result

[
    {
        "name": "cr1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "ASR9K"
    },
    {
        "name": "er1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "3945"
    }
]

Query 8

Perform an inner query. Braces de-reference the value of data in the queried object to be used for matching an object's key or an array's element.

Search for all devices in ATL of the NED value found in platform.ned.

{
  pass_on_null: true,
  query: "POPs.ATL[{platform.ned}]",
  obj: exampleObj
}

Query 8 Result

[
    {
        "name": "er1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "3945"
    },
    {
        "name": "sw1",
        "type": "switch",
        "make": "Cisco",
        "model": "Catalyt 2960G"
    }
]

Query Using the GET Helper Function

This example demonstrates a query against exampleObj2 using the get() helper function.

Example Object

const exampleObj2 = {
    "platform": {
        "ned": "cisco-ios",
        "description": "Cisco IOS Router or Switch"
    },
    "POPs": {
        "ATL:1": {
            "COUNTRY": "US",
            "ST": "GEORGIA",
            "LN": "ATLANTA",
            "cisco-ios-xr": [
                {
                    "name": "cr1.atl",
                    "type": "router",
                    "make": "Cisco",
                    "model": "ASR9K"
                }
            ],
            "cisco-ios": [
                {
                    "name": "er1.atl",
                    "type": "router",
                    "make": "Cisco",
                    "model": "3945"
                },
                {
                    "name": "sw1",
                    "type": "switch",
                    "make": "Cisco",
                    "model": "Catalyt 2960G"
                }
            ]
        },
        "ORF": {
            "COUNTRY": "US",
            "ST": "VIRGINIA",
            "LN": "NORFOLK",
            "cisco-ios": [
                {
                    "name": "cr1.orf",
                    "type": "router",
                    "make": "Cisco",
                    "model": "7600"
                }
            ],
            "juniper-junos": [
                {
                    "name": "er1.orf",
                    "type": "router",
                    "make": "Juniper",
                    "model": "MX10"
                }
            ]
        }
    }
};

Query 1

Perform a query for a property key that includes a special character. The get() helper function matches keys with special characters.

{
  pass_on_null: true,
  query: "POPs.:get(ATL:1)[**][*type=router]",
  obj: exampleObj2
}

Query 1 Result

[
    {
        "name": "cr1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "ASR9K"
    },
    {
        "name": "er1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "3945"
    }
]

Query 2

Search for any ATL:1 router in the given example object.

{
  pass_on_null: true,
  query: "POPs.:get(ATL:1)[**][*type=router]",
  obj: exampleObj2
}

Query 2 Result

[
    {
        "name": "cr1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "ASR9K"
    },
    {
        "name": "er1.atl",
        "type": "router",
        "make": "Cisco",
        "model": "3945"
    }
]

Single Depth Query and Nested Query

The query task can be used to extract a property out of an object. Sometimes this object may be nested within another property. Below is the reference object to be used in the query examples that follow.

Sample Reference Object

{
    "message": "Successfully retrieved product.",
    "data": {
        "_id": "622118d367d955aa5e22f1ed",
        "name": "Wolf Cola",
        "createdBy": "Frank",
        "lastUpdatedBy": "Glenn",
        "description": "The official drink of Boca Raton, FL."
    }
}

Single Depth Query Example

The example data is provided by the job variable obj. The object data being queried is the message.

Figure 4: Single Depth Query

The query task extracted the value for message("Successfully retrieved product."). Other tasks can now reference this value in the workflow from the output of the query task (output.return_data).

Figure 5: Single Depth Query Result

Nested Depth Query Example

The example data is provided by the job variable obj. The nested object data being queried is the data.createdBy.

Figure 6: Nested Query

The query task extracted the nested value for createdBy ("Frank"). Other tasks can now reference this value in the workflow from the query task's output (output.return_data).

Figure 7: Nested Query Result

Using the Pass on Null Variable

The following examples explain the interaction of the returned object and transition result of a query task using the pass_on_null flag.

Invalid Property: Pass on Null True

Using the same object from the examples above, the Reference variable is set to True, and an invalid property is queried.

Figure 8: Invalid Property: Pass on Null is True

Since it is an property that does not exist within the object, the query does not match anything and the entire object is returned as the result (i.e. a "pass-thru" of the original object). Additionally, the success transition is taken.

Figure 9: Invalid Property: Pass on Null is True Result

Invalid Property: Pass on Null False

In this example an invalid property is being queried and the Reference variable is set to False.

Figure 10: Invalid Property: Pass on Null is False

The query does not match anything and the value for the returned data is undefined. Because the invalid property is not in the object reference data and the Reference variable is set to False, the failure transition is taken.

Figure 11: Invalid Property: Pass on Null is False Result

Multiple Nested Invalid Property: Pass on Null Has No Effect

In this example, a property is being queried that is at least two (2) levels deep and neither layer exists in the object. The Reference variable setting has no effect on the outcome of the returned object nor the transition.

Figure 12: Pass on Null is False with Multiple Nested Invalid Props

The result of this query is a very unique circumstance because the value for the returned data is null with a success transition.