Query node
Category: Node
Version: 1.0
Last updated: November 19, 2025
Author: Any2Info
Description
The Query node allows you to execute SQLite queries against the output produced by the previous node in the data flow. All data passed from the preceding node is exposed through a virtual table named Dataflow, enabling you to further filter, transform, and select values as needed.
In addition to the Dataflow table, the node also provides access to any existing resultsets. Each resultset is stored internally as an SQLite table, allowing you to query them directly. This makes it possible to combine, join, or enrich the incoming data with data retrieved from these resultsets.
SQLite
Since the datahub operates locally—and may even run as an edge device—it must use a lightweight, embedded database engine to execute queries directly on the device. For this purpose, the datahub uses SQLite, which provides fast, serverless data processing with minimal resource requirements.
Because SQLite follows its own SQL dialect, some syntax and functions differ from systems such as SQL Server. As a result, certain SQL expressions may behave differently than users expect. Even so, SQLite offers a powerful and efficient environment for the filtering, transformation, and data-combination tasks performed within the Data Hub.
SQLite functions
SQLite supports a wide range of functions and SQL constructs that can be used to filter, and transform data within the datahub.
Below is an overview of commonly used SQLite functions and syntax elements that are relevant when writing queries in the Query node.
1. Selection and Limiting
LIMIT • Retrieves a specific number of rows (SQLite’s equivalent of TOP). • Example: SELECT * FROM table LIMIT 10
LIMIT … OFFSET • Used for paging (skip a number of rows, then return the next set). • Example: LIMIT 10 OFFSET 20
2. String Operations
|| • Concatenates two or more strings. • Example: firstName || ' ' || lastName
substr(column, start, length) • Returns a substring starting at position 1. • Example: substr(name, 1, 3)
length(column) • Returns the number of characters in a string.
upper(column) • Converts a string to uppercase.
lower(column) • Converts a string to lowercase.
TRIM(column) • Removes leading and trailing whitespace.
LTRIM(column) • Removes whitespace from the left side of the string.
RTRIM(column) • Removes whitespace from the right side of the string.
TRIM(column, 'x') • Removes a specific character from both sides. • Example: TRIM(column, '.')
3. Joins
INNER JOIN • Returns rows that exist in both tables. • Example: SELECT … FROM A JOIN B ON A.id = B.fk
LEFT JOIN • Returns all rows from the left table and matching rows from the right table. • Example: SELECT … FROM A LEFT JOIN B ON A.id = B.fk
Not supported in SQLite • RIGHT JOIN • FULL OUTER JOIN • CROSS APPLY / OUTER APPLY
4. Type Conversions
CAST(column AS INTEGER) • Converts a value to an integer.
CAST(column AS REAL) • Converts a value to a floating-point number (double).
CAST(column AS TEXT) • Converts a value to a string.
5. Date and Time Functions
date('now') • Returns the current date (YYYY-MM-DD).
time('now') • Returns the current time (HH:MM:SS).
datetime('now') • Returns the current date and time.
strftime(format, value) • Formats a date or time value. • Example: strftime('%Y-%m-%d', 'now')
date('now', '+x days') • Adds or subtracts days. • Example: date('now', '+7 days')
datetime('now', '-x hours') • Adds or subtracts hours. • Example: datetime('now', '-1 hour')
6. Logical Operations and NULL Handling
CASE WHEN … THEN … END • Conditional logic for branching.
IFNULL(value, fallback) • Returns the fallback value if the first value is NULL (similar to ISNULL in SQL Server).
COALESCE(a, b, …) • Returns the first non-NULL value from the list.
7. Collations and Case Sensitivity
COLLATE NOCASE • Performs case-insensitive comparison. • Example: column = 'abc' COLLATE NOCASE
COLLATE BINARY • Performs case-sensitive comparison. • Example: column = 'abc' COLLATE BINARY
Tips & Best Practices
Be aware of SQLite-specific syntax. Some SQL Server features (such as RIGHT JOIN or FULL OUTER JOIN) are not available, and certain functions behave differently.
Use the Dataflow table as the primary input for your query, and only reference result sets when needed.
Errors & Troubleshooting
• Syntax differences from SQL Server. Errors may appear if SQL Server syntax is used. Features like RIGHT JOIN, FULL OUTER JOIN, or certain functions are not supported in SQLite.
Changelog
1.0
December 04, 2025
Initial documentation version added.
Last updated