Articles in this section

Built-in SQL in CLI

  • Updated
Follow

The Etlworks CLI includes a compact SQL engine designed specifically for querying, filtering, flattening, and transforming the JSON returned by CLI commands.

It behaves like SQL but operates directly on in-memory JSON objects, making it ideal for:

  • exploring Etlworks objects

  • slicing subsets of returned data

  • flattening nested structures

  • building automation steps

  • extracting values for variable capture

  • transforming CLI results into structured data

SQL is applied directly in the same CLI statement, for example:

list-flows select id,name where flowType like '%cdc%' order by name

Why Built-in SQL Is

Works with any command that returns JSON

Most CLI command returns structured JSON.

Examples:

  • list-connections

  • list-flows

  • flow-executions-aggregated

  • get-flow

  • API calls via endpoints

  • Any command returning arrays or objects

SQL operates on the JSON produced by the command.

Examples:

list-flows where flowType like '%postgres%'
find-connection-usage 123 select id,name,type where name='test'
list-tenants select id,name limit 5

JSON Access Paths

The SQL engine supports full JSON path access:

meta.createdAt
stats.totalRecords
agent.info.version
items[0].value
addresses[0].street

Paths are:

  • case-insensitive

  • support nested objects

  • support arrays via field[index]

  • support multiple array levels (items[0][2].name)

Supported SQL Clauses

The SQL engine supports the most useful SQL constructs:

  • SELECT (with alias support and functions)

  • DISTINCT

  • FROM (flattening)

  • WHERE (full expression language)

  • ORDER BY (multi-field, asc/desc)

  • LIMIT

These clauses may appear in this order:

<command> ... select ... ... from ... where ... order by ... limit

Example:

flow-executions-aggregated offset=5 
select flowId, auditId, started, ended 
from executions
where started > ts("now - 10d") 
order by ended desc 
limit 5

SELECT

Forms:

select *
select id,name
select id,name as tenantName
select upper(name) as uname
select date(createdAt)
select distinct name
select parent.*, started

SELECT supports:

  • multiple fields

  • alias with or without AS

  • nested JSON paths

  • scalar functions

  • DISTINCT

Example:

list-connections scope=all
select id,name,connectionType,date(created) as created
where connectionType like '%mysql%'
order by created desc
limit 10

FROM (Flattening Arrays)

FROM extracts and flattens elements from nested arrays.

Example: flatten executions:

flow-executions-aggregated offset=5
select parent.id, parent.name, started, ended
from executions
where started > ts("now - 10d")

JSON path in from is supported:

select x, y from node1.node2

Parent access is supported:

select parent.id, parent.name, v from node1

Or pull all parent fields:

select parent.*, started from executions

WHERE

WHERE supports a full expression engine.

Boolean logic

a AND b
a OR b
NOT a
(A AND (b OR c))

Comparisons

= != > >= < <=

NULL checks

field is null
field is not null

LIKE (case-insensitive)

name like '%corp%'
email not like '%.test'

IN / NOT IN

type in ("mysql","postgres","sqlserver")

BETWEEN

createdAt between "2024-01-01" and "2024-03-01"

Functions inside WHERE

where startswith(name,"A")
where addDays(ts,1) < now()
where parsedate(created) > ts("now - 2h")

ORDER BY

order by id
order by name desc
order by createdAt desc, name asc

ORDER BY supports:

  • numbers

  • strings

  • booleans

  • timestamps

  • SELECT aliases

select name as n order by n desc

LIMIT

limit 5
limit 1
limit 100

Functions

String functions

upper(value) 
lower(value) 
startswith(value, prefix) 
endswith(value, suffix)

Time helpers (epoch millis)

addSeconds(ts, n) 
addMinutes(ts, n) 
addHours(ts, n) 
addDays(ts, n)

Parsing and formatting

parseDate(text) - auto detect common formats, return epoch millis 
parseDate(text, pattern) - parse with explicit SimpleDateFormat pattern 
age(ts) - human readable age, for example "5m ago"

Timestamp helpers

ts(value) - normalize various inputs to epoch millis 
now() - current epoch millis 
date(ts) - ISO-8601 UTC string, for example "2024-01-01T10:20:30Z" 
date_only(ts) - "yyyy-MM-dd" in UTC * time_only(ts) - "HH:mm:ss" in UTC 
unix(value) - alias for ts(value), returns epoch millis

Relative timestamp syntax for ts / unix / any toEpochMillis(String)

ts(now)
ts("now - 1d") 
ts("now + 12h") 
ts("now - 15m") 
ts("now + 20s") 
ts("now - 50ms")

Related articles