query
  • 18 Oct 2024
  • Dark
    Light
  • PDF

query

  • Dark
    Light
  • PDF

Article summary

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.

Incoming Type Required Description
pass_on_null Boolean Yes Determines the query task results for both its return_data as well as which transition (success or failure) it takes if the query fails. Please review Using the Pass On Null Variable below for additional detail.
query String Yes Specifies the query expression. It is similar to how you may perform lookups in JavaScript. Consult the json-query NPM page for usage instructions or review the examples for this task. The query expression can reference a helper function :get() when accessing the value of a property in which the key includes special characters. The colon ( : ) is a special character; it is used to delineate JSON keys and values. If the JSON property key includes a colon, query the property value with the get() helper function. Reference the example below (Query 9) for syntax. The value can come from a job variable, a static value, or an outgoing variable in an earlier task. If the reference task is a job variable or earlier task, the reference variable shows a dropdown of available variable names for selection. If the reference task is static, the most common setting, the reference variable becomes a text edit box.
obj Object Yes Identifies the data to query. The object can come from a job variable, a static value, or an outgoing variable in an earlier task. If the reference task is a job variable or earlier task, the reference variable shows a dropdown of available variable names for selection. If the reference task is static, the reference variable becomes a text edit box.

Outgoing Description
return_data The query results are assigned to an immutable outgoing variable return_data. Optionally, you can create a job variable from return_data. The immutable outgoing variable return_data remains, and a new mutable job variable is created. When creating a job variable, provide a new name to avoid name conflicts and provide clarity.

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
17_workflow_restcall_23_2

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
18_queryNumbersAPI_23_2

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
19_output_queryNumbersAPI_23_2

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
20_single_depth_query_23_2

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
22_single_deptyh_query_result_23_2

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
21_nested_view_23_2

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
23_nested_view_23_2

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
24_pass_on_null_23_2

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
25-pass-on-null-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
26_pass_on_null_23_2

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
27%20-pass-on-null-false_result_23_2

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
28_multi_nest_pass_on_null_23_2

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

Warning: Take special precaution when using this scenario as it is very counter-intuitive.


Was this article helpful?

What's Next
Changing your password will log you out immediately. Use the new password to log back in.
First name must have atleast 2 characters. Numbers and special characters are not allowed.
Last name must have atleast 1 characters. Numbers and special characters are not allowed.
Enter a valid email
Enter a valid password
Your profile has been successfully updated.