Skip to main content

Query

Filters a data table using a query string.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs​

  • Table - The input data table to be filtered.
  • Query String - The query string used to filter the table.

Options​

  • Output Type - Specifies whether to pass the table by reference or by value. Options are:
    • Pass By Reference
    • Pass By Value

Output​

  • Table - The resulting filtered data table.

How It Works​

The Query node filters a data table using a query string. When executed, the node:

  1. Validates that the input table is not empty and is valid
  2. Checks if the table is a reference table and handles it appropriately
  3. Validates that the query string is not empty
  4. Converts the data table to a pandas DataFrame
  5. Applies the query string to filter the DataFrame
  6. Converts the filtered DataFrame back to the data table format
  7. Returns the filtered table

Requirements​

  • A valid input data table
  • A valid query string compatible with pandas query syntax

Error Handling​

The node will return specific errors in the following cases:

  • Empty or invalid input table
  • Empty query string
  • Invalid table structure
  • Invalid query string syntax

Usage Notes​

  • The Output Type option can be set to "Pass By Reference" for handling large tables more efficiently
  • The query string should follow pandas query syntax
  • Examples of query strings:
    • "column_name == 'value'" - Filter rows where column_name equals 'value'
    • "column_name > 10" - Filter rows where column_name is greater than 10
    • "column1 == 'value1' & column2 > 5" - Filter rows matching multiple conditions
  • The node supports all standard pandas query operations

Usage Examples​

Example 1: Filter by a Numeric Comparison​

.then('a06926', 'Robomotion.Pandas.Query', 'Big Orders', {
  inTable: Message('table'),
  inQueryString: Custom('amount > 1000'),
  outTable: Message('filtered')
})

Example 2: Combine Conditions​

.then('b17c34', 'Robomotion.Pandas.Query', 'Open UK Orders', {
  inTable: Message('table'),
  inQueryString: Custom("country == 'UK' and status == 'open'"),
  outTable: Message('filtered')
})

Example 3: Filter Against a Flow Variable​

Build the expression in a Function node first, so a value from earlier in the flow can be used:

- Function: msg.q = "customer == '" + msg.customerName + "'"
- Query (table, msg.q) -> filtered

Example 4: Filter Then Export​

- CSV To Data Table (orders.csv) -> table
- Query (table, "amount > 1000 and status != 'cancelled'") -> filtered
- Sort Table (filtered, "amount", Descending) -> filtered
- Data Table To CSV (filtered, large-orders.csv)

Tips​

  • The expression is a pandas query string, not SQL. Use == not =, and/or not AND/OR, and no SELECT or WHERE keyword
  • Wrap string literals in single quotes inside the expression, and use double quotes around the whole expression in TypeScript so the quoting does not collide
  • Column names with spaces need backticks: `order total` > 100
  • A column that arrived from CSV as text compares as text - '9' > '10' is true. Run Convert Type first for numeric comparisons
  • A query matching nothing returns an empty table rather than an error, so check the row count before assuming the next node has data
  • Use .str.contains('foo') for partial text matching, and .isna() to find blank cells