# SODA Query (@j3lte/soda)

> A read-only Socrata Open Data API (SODA 2.1) query client for Deno and Node. Build SoQL queries with a fluent, type-safe builder — select, where, group, order, pagination — and fetch the results as JSON, GeoJSON, or CSV.

This library targets the Socrata SODA 2.1 endpoints (`/resource/{id}.json`) and fetches data only — it does not create, update, or delete, and does not use the SODA 3.0 (`/api/v3/...`) endpoints. Published on JSR as `@j3lte/soda` and on npm as `soda-query`. Query construction is fully covered by JSDoc with examples; the JSR doc pages below are generated from that source.

**Install** — Deno: `deno add jsr:@j3lte/soda` · Node: `npm i soda-query`. All fetches resolve to `{ data, error, status }` (errors are returned, not thrown).

**Create a query:**

```ts
import { createQueryWithDataset, SodaQuery } from "jsr:@j3lte/soda";

const query = new SodaQuery("data.cityofnewyork.us").withDataset("erm2-nwe9");
// same as:
const q2 = createQueryWithDataset("data.cityofnewyork.us", "erm2-nwe9");

// Typed rows:
type Row = { agency: string; complaint_type: string };
const typed = new SodaQuery<Row>("data.cityofnewyork.us").withDataset("erm2-nwe9");
```

**Authentication** (second constructor arg):

```ts
new SodaQuery("data.cityofnewyork.us", { apiToken: "APP_TOKEN" }); // raises rate limit
new SodaQuery("data.cityofnewyork.us", { username: "u", password: "p" }); // Basic
new SodaQuery("data.cityofnewyork.us", { accessToken: "OAUTH" }); // OAuth
new SodaQuery("data.cityofnewyork.us", {}, { strict: true }); // options arg
```

**A full query** — strings are raw SoQL clauses; helpers are type-safe:

```ts
import { Order, SodaQuery, Where } from "jsr:@j3lte/soda";

const { data, error, status } = await new SodaQuery("data.cityofnewyork.us")
  .withDataset("erm2-nwe9")
  .select("agency", "borough", "complaint_type")
  .where(
    Where.and(
      Where.like("complaint_type", "Noise%"),
      Where.gt("created_date", "2019-01-01T00:00:00.000"),
    ),
  )
  .orderBy(Order.by("created_date").desc)
  .limit(10)
  .execute();
```

**Select** (`$select` transforms):

```ts
import { Select, SelectCase, Where } from "jsr:@j3lte/soda";

Select("amount").sum().as("total"); // sum(amount) as total
Select("name").count().as("n"); // count(name) as n
Select("value").avg(); // avg(value)
Select("name").upperCase(); // upper(name)
Select("name").unaccent(); // unaccent(name)
Select("value").log(); // ln(value)
Select("created_date").dateExtractYear(); // date_extract_y(created_date)
Select("the_geom").convexHull(); // convex_hull(the_geom)

// Conditional case(...). Trailing ["true", ...] is the default.
SelectCase(
  [Where.gt("score", 90), "A"],
  [Where.gt("score", 80), "B"],
  ["true", "F"],
).as("grade");
```

**Where** (`$where` filters):

```ts
import { Where } from "jsr:@j3lte/soda";

Where.eq("borough", "MANHATTAN"); // borough = 'MANHATTAN'
Where.ne("status", "Closed"); // status != 'Closed'
Where.gt("score", 80); // score > 80  (also gte/lt/lte)
Where.between("score", 50, 100); // score between 50 and 100
Where.in("borough", "MANHATTAN", "BROOKLYN"); // borough in ('MANHATTAN','BROOKLYN')
Where.notIn("status", "Closed", "Pending");
Where.like("complaint_type", "Noise%"); // complaint_type like 'Noise%'
Where.isNull("closed_date"); // closed_date IS NULL
Where.isNotNull("closed_date");
Where.startsWith("complaint_type", "Noise"); // starts_with(complaint_type, 'Noise')

// Combine
Where.and(Where.eq("borough", "BRONX"), Where.or(Where.eq("s", "Open"), Where.eq("s", "Pending")));
Where.from({ borough: "BRONX", status: "Open" }); // all AND-ed equals
Where.field("score").gt(80); // bind a field once

// Geospatial (Location / Point / Line / Polygon / Multi*)
Where.withinBox("the_geom", 40.78, -73.98, 40.74, -73.94);
Where.withinCircle("the_geom", 40.7128, -74.006, 1000); // radius meters
Where.withinPolygon("the_geom", "MULTIPOLYGON (((...)))"); // WKT, longitude-first
Where.intersects("the_geom", "POINT (-73.98 40.75)");
```

**Typed fields** (`Field` + `DataType`) give compile-time checks:

```ts
import { DataType, Field, Select } from "jsr:@j3lte/soda";

Field("score", DataType.Number);
query.select(Select(Field("name", DataType.Text)).as("alias")); // ok
query.select(Select(Field("name", DataType.Text)).avg()); // throws: avg on Text
```

**Order, group, having, search:**

```ts
import { Order, Select, Where } from "jsr:@j3lte/soda";

query.orderBy(Order.by("created_date").desc, Order.by("agency").asc); // .asc/.desc are getters
query.orderBy("created_date DESC"); // string works too

query
  .select(Select("borough"), Select("amount").sum().as("total"))
  .groupBy("borough")
  .having(Where.gt("total", 1000)); // having needs a groupBy

query.search("noise"); // full-text $q
query.withSystemFields(); // include :id / :created_at / :updated_at
```

**Arithmetic expressions** (`expr`, parenthesized for safe nesting):

```ts
import { expr, Select } from "jsr:@j3lte/soda";

expr.mul("price", "qty"); // (price * qty)
expr.div(expr.add("a", "b"), 2); // ((a + b) / 2)   — also sub/mod/pow
query.select(Select(expr.mul("price", "qty")).as("total"));
```

**Fetching results:**

```ts
const { data, error, status } = await query.execute(); // Array of rows
const { data: row } = await query.single(); // first row or null
const { data: total } = await query.count(); // matching row count (number)
const { data: cols } = await query.getColumns(); // [{ fieldName, dataTypeName, ... }]
const { data: geojson } = await query.executeGeoJSON(); // FeatureCollection
const { data: csv } = await query.executeCSV(); // raw CSV string

// Response headers from the last request
await query.execute();
query.headers.lastModified;
query.headers.etag;
```

**Pagination** ($limit defaults to 1000/request; set a stable order for full scans):

```ts
for await (const page of query.pages({ pageSize: 1000 })) { /* array per page */ }
for await (const row of query.rows()) { /* one row at a time */ }
const { data: all } = await query.executeAll({ max: 50000 }); // eager, optional cap
```

**Stored queries** (snapshot + reuse):

```ts
query.select("a").where("a > 1").prepare("first").clear();
query.select("b").prepare("second");
await query.execute("first");
query.getURL("second"); // inspect the built URL
```

## Docs

- [JSR package (README, install, examples)](https://jsr.io/@j3lte/soda): overview, installation (`deno add jsr:@j3lte/soda`), and usage examples.
- [API reference — all symbols](https://jsr.io/@j3lte/soda/doc): generated from JSDoc, each method with an `@example`.
- [SodaQuery](https://jsr.io/@j3lte/soda/doc/~/SodaQuery): the chainable query builder and HTTP client — `select`, `where`, `having`, `groupBy`, `orderBy`, `limit`/`offset`, `search`, `simple`, `soql`, and terminal methods `execute`, `single`, `pages`/`rows`/`executeAll`, `count`, `getColumns`, `executeCSV`, `executeGeoJSON`, `getMetaData`.
- [Where](https://jsr.io/@j3lte/soda/doc/~/Where): `$where` filter builder — `eq`/`ne`/`gt`/`gte`/`lt`/`lte`, `isNull`/`isNotNull`, `in`/`notIn`, `like`/`notLike`, `between`/`notBetween`, `and`/`or`, `field`, geospatial `withinBox`/`withinCircle`/`withinPolygon`/`intersects`/`startsWith`.
- [Select](https://jsr.io/@j3lte/soda/doc/~/Select): `$select` builder — plain fields, aliases (`as`), aggregates (`count`/`avg`/`sum`/`min`/`max`/`distinct`/`stddev`), numeric/text/date/geospatial functions.
- [SelectCase](https://jsr.io/@j3lte/soda/doc/~/SelectCase): build SoQL `case(...)` expressions from `[condition, value]` pairs.
- [expr](https://jsr.io/@j3lte/soda/doc/~/expr): raw expression helpers — boolean `and`/`or` and arithmetic `add`/`sub`/`mul`/`div`/`mod`/`pow` (`+ - * / % ^`), parenthesized for safe nesting.
- [Field and DataType](https://jsr.io/@j3lte/soda/doc/~/DataType): typed field objects and the Socrata data types (with per-type docs and version availability).

## Source

- [GitHub repository](https://github.com/j3lte/deno-soda): source, issues, and tests.
- [CHANGELOG](https://github.com/j3lte/deno-soda/blob/main/CHANGELOG.md): release history.
- [Socrata SoQL documentation](https://dev.socrata.com/docs/): the upstream API this client targets.

## Optional

- [npm package (soda-query)](https://www.npmjs.com/package/soda-query): Node.js usage.
- [README on GitHub](https://github.com/j3lte/deno-soda/blob/main/README.md): the same content rendered on GitHub.