With --format json the schema is `{ columns: string[], rows: Array<Record<string, string|null>>, row_count: number, execution_time_ms: number }`. Rows are objects keyed by column name; row values are JSON strings (or null), never typed numbers.
delta-forge-cli --format json query <SQL> | jq <SELECTOR>## Overview `--format json` emits a stable document that jq can slice into scalars, rows, or aggregates. The schema uses snake_case field names at the top level and returns `rows` as an array of objects keyed by column name. Every row value is either JSON `null` (for SQL NULL) or a JSON string; numbers, dates, decimals, and booleans are all serialised as strings. Top-level `row_count` and `execution_time_ms` are JSON numbers. ## Recipe Extract an engine-reported row count, guard against empty results, and compare a derived scalar as a number: ```bash #!/usr/bin/env bash set -euo pipefail payload=$( delta-forge-cli --profile prod --format json \ query 'SELECT count(*) AS n, max(updated_at) AS last_ts FROM orders' ) count=$(printf '%s' "$payload" | jq -r '.rows[0].n | tonumber') last_ts=$(printf '%s' "$payload" | jq -r '.rows[0].last_ts') if (( count == 0 )); then echo 'no rows' >&2 exit 1 fi echo "count=$count last_ts=$last_ts" ``` ## Schema reminder ```json { "columns": ["n", "last_ts"], "rows": [ { "n": "42", "last_ts": "2026-04-19 12:34:56" } ], "row_count": 1, "execution_time_ms": 17 } ``` - `rows[i]` is a JSON object, not an array. `rows[0][0]` is `null`, not the first column's value. - Value types inside each row object are `string` or `null`. Use `| tonumber`, `| tostring`, or `| fromdate` in jq to produce typed values. - Column name keys preserve SQL aliases. An unaliased aggregate such as `count(*)` will have an engine-assigned name, so aliasing (`count(*) AS n`) is the reliable way to write selectors. ## Behavior - `.rows` is capped at the client-side `max_rows` (100 by default); `.row_count` is the full engine count. Compare the two to detect truncation. - Tools that parsed an earlier camelCase shape (`rowCount`, `executionTimeMs`) must be updated to snake_case (`row_count`, `execution_time_ms`). - Empty results still parse cleanly: `{ "columns": [], "rows": [], "row_count": 0, "execution_time_ms": N }`.
# Extract a single scalar by column name. Alias the column so the key is predictable:
delta-forge-cli --format json query 'SELECT count(*) AS n FROM orders' | jq -r '.rows[0].n'# Scalar as a typed number (row values are always strings, so cast):
delta-forge-cli --format json query 'SELECT count(*) AS n FROM orders' | jq -r '.rows[0].n | tonumber'# Grab the first column of the first row without knowing its name:
delta-forge-cli --format json query 'SELECT count(*) FROM orders' | jq -r '.rows[0] | to_entries[0].value'# Iterate every row object:
delta-forge-cli --format json query 'SELECT id, status FROM jobs' | jq -c '.rows[]'# Fail the script if the result set is empty (uses the engine-reported count):
[ "$(delta-forge-cli --format json query 'SELECT 1 FROM orders LIMIT 1' | jq '.row_count')" -gt 0 ] || exit 1# Detect truncation against the default 100-row cap:
delta-forge-cli --format json query 'SELECT * FROM big_table' | jq 'if (.rows | length) < .row_count then "truncated" else "complete" end'