Filter & select one group¶

Available in: Python · Node · DuckDB · Browser

Narrow one group to the rows and columns you want. In Python it's a lazy query builder; on every other surface it's the same idea said in SQL — and because the dtype is the AGS type, a numeric filter compares numbers, not strings.

PythonNodeDuckDBBrowser

# what this shows: the lazy single-result AgsQuery builder + its four terminals.
import laterite
import pandas as pd
import polars as pl
from duckdb import DuckDBPyRelation

# Build a query lazily: nothing runs until a terminal is called.
q = (
    laterite.read("examples/sample_site.ags")
    .query("SELECT * FROM LOCA")
    .filter("LOCA_GL > 28")
    .select("LOCA_ID", "LOCA_TYPE", "LOCA_GL")
)

# Four terminals materialise the same lazy plan:
frame = q.frame()  # handle's default backend (polars)
pl_df = q.to_polars()  # always polars
pd_df = q.to_pandas()  # always pandas
rel = q.relation()  # the lazy DuckDBPyRelation (not yet materialised)

print(pl_df)

# Projection is honoured exactly as declared.
assert frame.columns == ["LOCA_ID", "LOCA_TYPE", "LOCA_GL"]
assert pl_df.columns == ["LOCA_ID", "LOCA_TYPE", "LOCA_GL"]

# Each terminal returns its expected type.
assert isinstance(frame, pl.DataFrame)
assert isinstance(pl_df, pl.DataFrame)
assert isinstance(pd_df, pd.DataFrame)
assert isinstance(rel, DuckDBPyRelation)

shape: (7, 3)
┌─────────┬───────────┬─────────┐
│ LOCA_ID ┆ LOCA_TYPE ┆ LOCA_GL │
│ ---     ┆ ---       ┆ ---     │
│ str     ┆ str       ┆ f64     │
╞═════════╪═══════════╪═════════╡
│ BH02    ┆ RC        ┆ 32.49   │
│ BH03    ┆ RC        ┆ 28.54   │
│ BH04    ┆ RC        ┆ 29.04   │
│ BH05    ┆ RC        ┆ 31.62   │
│ BH07    ┆ RC        ┆ 31.33   │
│ BH08    ┆ CP        ┆ 28.67   │
│ BH09    ┆ CP        ┆ 30.98   │
└─────────┴───────────┴─────────┘

.query(sql) returns a lazy AgsQuery. Chain .filter(...) to drop rows and .select(...) to pick columns; nothing runs until a terminal pulls the result. The four terminals materialise the same plan: .frame() (the handle's default backend, polars), .to_polars(), .to_pandas(), and .relation() (the raw DuckDBPyRelation, still lazy). Because the dtype IS the AGS type, LOCA_GL > 28 compares numbers, not strings.

Each builder call returns a new immutable AgsQuery — .filter(...) and .select(...) never mutate in place, so you can branch off a shared base query without one fork bleeding into another:

base = ags.query("SELECT * FROM LOCA")
rotary = base.filter("LOCA_TYPE = 'RC'")     # base is untouched
deep   = base.filter("LOCA_GL > 30")         # independent of `rotary`

Use .query() for guard-railed single-group work. When you need a real join across groups, reach for .sql(...) instead — see SQL across groups.

// what this shows: sql() as Node's equivalent of Python's lazy query builder — filter + select live in the SQL.
// Needs the optional @duckdb/node-api peer (npm i @duckdb/node-api) — read/validate/fix don't.
import { read } from "laterite";
import assert from "node:assert/strict";

const ags = read("examples/sample_site.ags");

// Node has one query door — sql() — not a lazy .query()/.filter()/.select() chain:
// narrow rows and columns in the statement itself. The dtype IS the AGS type, so
// `LOCA_GL > 28` compares numbers, not strings.
const rows = await ags.sql(
  "SELECT LOCA_ID, LOCA_TYPE, LOCA_GL FROM LOCA WHERE LOCA_GL > 28 ORDER BY LOCA_ID",
);
console.log(rows);
ags.close();

assert.equal(rows.length, 7);
assert.deepEqual(Object.keys(rows[0]), ["LOCA_ID", "LOCA_TYPE", "LOCA_GL"]);
assert.ok(rows.every((r) => r.LOCA_GL > 28)); // numeric compare, not lexical

[
  { LOCA_ID: 'BH02', LOCA_TYPE: 'RC', LOCA_GL: 32.49 },
  { LOCA_ID: 'BH03', LOCA_TYPE: 'RC', LOCA_GL: 28.54 },
  { LOCA_ID: 'BH04', LOCA_TYPE: 'RC', LOCA_GL: 29.04 },
  { LOCA_ID: 'BH05', LOCA_TYPE: 'RC', LOCA_GL: 31.62 },
  { LOCA_ID: 'BH07', LOCA_TYPE: 'RC', LOCA_GL: 31.33 },
  { LOCA_ID: 'BH08', LOCA_TYPE: 'CP', LOCA_GL: 28.67 },
  { LOCA_ID: 'BH09', LOCA_TYPE: 'CP', LOCA_GL: 30.98 }
]

Node has one query door — sql() — not a lazy .query()/.filter()/.select() chain: the filter and the projection live in the statement. It's async and returns plain row objects; LOCA_GL arrives as a real JS number, so the > 28 in the SQL is a numeric comparison. sql() is the one Node feature behind an optional peer — npm i @duckdb/node-api — so services that only read/validate/fix never pull a database in; close() releases the connection. For a real cross-group join it's the same sql() — see SQL across groups.

-- Narrow one group to rows and columns — filter + select are plain SQL over read_ags.
-- The dtype IS the AGS type, so `loca_gl > 28` compares numbers, not strings.
-- expect-rows: 7
SELECT loca_id, loca_type, loca_gl
FROM read_ags('examples/sample_site.ags', 'LOCA')
WHERE loca_gl > 28
ORDER BY loca_id;

read_ags(file, 'LOCA') is a table function; the filter and the projection are plain SQL over it. Columns come back born-typed, so loca_gl > 28 compares numbers exactly as the other surfaces do. To narrow across groups, add more read_ags(...) calls and join — see SQL across groups.

Open the web app and load your file into the Explore pane. Pick a group, then filter its rows and choose columns interactively — the grid is fed by the same born-typed columns, so a numeric filter sorts and compares as numbers. The file never leaves your machine, which keeps confidential ground-investigation data local.

Every door narrows the same born-typed group: Python's builder stays lazy and composable, Node and DuckDB say it in SQL, the browser does it by point-and-click. Reach for .query()/sql() on one group; step up to a join across groups with SQL across groups.

See also: SQL across groups · Chaining.