Query table in Datastore
Query Datastore using SQL. Wrk Action uses SQLite syntax. Note: To retrieve the row ID, include the _id field in your SELECT clause.
Application
- Datastore
Inputs (what you have)
| Name | Description | Data Type | Required? | Example |
|---|---|---|---|---|
| SQL query | SQL Statement to fetch data from Datastore. Note: To retrieve the row ID, include the _id field in your SELECT clause. | Text (Long) | Yes | SELECT * FROM DataTable1 |
Outputs (what you get)
| Name | Description | Data Type | Required? | Example |
|---|---|---|---|---|
| Total number of rows | The total number of rows in the output JSON | Number | No | |
| Output JSON | JSON output of the query result | Text (Long) | No |
Outcomes
| Name | Description |
|---|---|
| Success | This status is selected if the job has successfully completed. |
SQL Configuration Instructions
Wrk supports a subset of SQLite-style SQL queries for retrieving, filtering, and aggregating data.
This allows you to perform common data operations such as selecting fields, applying filters, grouping, and sorting results — all using familiar SQL syntax.
The supported syntax covers most analytical and filtering scenarios but excludes advanced database operations (like subqueries or window functions).
Supported Clauses
SELECT Clause
Syntax
SELECT field1, field2, ... SELECT field AS alias SELECT SUM(field), COUNT(*), AVG(field), MIN(field), MAX(field) SELECT * FROM table
Meaning
Use SELECT to define which fields to return.
Aggregate functions like SUM, AVG, MIN, MAX, and COUNT are supported.
Aliasing (AS alias) can rename fields in the output.
| Function | Description |
|---|---|
SUM(x) | Adds up all values of x |
AVG(x) | Returns the average of x |
MIN(x) | Returns the smallest value of x |
MAX(x) | Returns the largest value of x |
COUNT(*) | Counts total rows |
Examples
✅ Basic Selection
SELECT name, age FROM users
✅ Aggregation
SELECT department, COUNT(*) AS total FROM employees GROUP BY department
FROM Clause
Syntax
FROM table FROM table AS alias
Defines the dataset to query.
Table aliases are supported and can be used to simplify references in joins or where conditions.
✅ Example
SELECT u.name FROM users AS u
WHERE Clause
Syntax
WHERE field = value WHERE field > 10 WHERE field BETWEEN 10 AND 20 WHERE name LIKE '%john%' WHERE date >= '2024-01-01' WHERE status IN ('active', 'pending') WHERE amount NOT IN (0, NULL) WHERE field IS NULL WHERE field IS NOT NULL
Supported Operators
| Operator | Description |
|---|---|
= / == | Equal to |
!= / <> | Not equal to |
> / < | Greater / Less than |
>= / <= | Greater or equal / Less or equal |
IN (...) | Value exists in a list |
NOT IN (...) | Value does not exist in a list |
LIKE | Pattern match (% for any string, _ for a single character) |
IS NULL | Field is missing or null |
IS NOT NULL | Field is present and not null |
AND, OR | Combine multiple conditions |
Examples
✅ Numeric Comparison
SELECT * FROM orders WHERE total > 100
✅ Pattern Matching
SELECT * FROM users WHERE email LIKE '%@example.com'
✅ Combined Conditions
SELECT * FROM users WHERE active = true AND age >= 18
✅ Null Check
SELECT * FROM logs WHERE deleted_at IS NULL
JOIN Clause
Syntax
SELECT * FROM users JOIN orders ON users.id = orders.user_id
Supports simple equality joins between tables using JOIN ... ON.
Each join is equivalent to a relational lookup between matching fields.
✅ Example
SELECT u.name, o.total FROM users AS u JOIN orders AS o ON u.id = o.user_id
GROUP BY Clause
Syntax
GROUP BY field1, field2
Used with aggregation functions to summarize data into groups.
✅ Example
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department
ORDER BY Clause
Syntax
ORDER BY field ASC ORDER BY field DESC
Sorts results in ascending (ASC) or descending (DESC) order.
Multiple sort fields are supported.
✅ Example
SELECT name, created_at FROM users ORDER BY created_at DESC
LIMIT and OFFSET Clauses
Syntax
LIMIT n OFFSET n
Use LIMIT to restrict the number of rows returned, and OFFSET to skip a certain number of rows before returning results.
✅ Example
SELECT name, email FROM users LIMIT 10 OFFSET 20
Data Type Handling
When filtering or comparing values, Wrk automatically converts SQL literals into appropriate types.
| Example Input | Interpreted As |
|---|---|
'text' | String |
"text" | String |
123 | Integer |
3.14 | Float |
true / false | Boolean |
'2025-01-01T00:00:00Z' | DateTime |
NULL | Null value |
Date strings must follow ISO 8601 format (YYYY-MM-DD or YYYY-MM-DDTHH:MM:SSZ).
Supported Features Summary
| Feature | Supported | Example |
|---|---|---|
SELECT | ✅ | SELECT field1, field2 |
Aggregate Functions | ✅ | SELECT COUNT(*) FROM ... |
WHERE | ✅ | WHERE field = 5 |
LIKE | ✅ | WHERE name LIKE '%test%' |
IN / NOT IN | ✅ | WHERE field IN ('A', 'B') |
IS NULL / IS NOT NULL | ✅ | WHERE field IS NOT NULL |
AND / OR | ✅ | WHERE a = 1 AND b = 2 |
GROUP BY | ✅ | GROUP BY department |
ORDER BY | ✅ | ORDER BY date DESC |
LIMIT / OFFSET | ✅ | LIMIT 10 OFFSET 5 |
JOIN | ✅ | JOIN orders ON users.id = orders.user_id |
| Subqueries | ❌ | Not supported |
| UNION / INTERSECT | ❌ | Not supported |
| HAVING | ❌ | Not supported |
| Window Functions | ❌ | Not supported |
Example Queries
✅ Example 1: Filter and Sort
SELECT name, age FROM users WHERE age >= 18 ORDER BY name ASC LIMIT 5
✅ Example 2: Aggregation with Grouping
SELECT department, COUNT(*) AS total FROM employees WHERE active = true GROUP BY department ORDER BY total DESC
✅ Example 3: Join Between Tables
SELECT u.name, COUNT(o.id) AS total_orders FROM users AS u JOIN orders AS o ON u.id = o.user_id GROUP BY u.name ORDER BY total_orders DESC
Notes
- Queries must follow standard SQLite formatting.
- Unsupported syntax will return an error.
- Quotation marks (
'or") around string literals are interchangeable. - Date comparisons and pattern matching (
LIKE) are case-insensitive by default.