Filter JSON files — Sails [Sails](https://sails.app/) / [Guides](/guides/) [Contact](https://sails.app/contact) On this site [Overview](/guides/) How to * [All how-tos](/guides/how-to) * [Publish a site](/guides/how-to/publish-a-site) * [Check a site before you publish](/guides/how-to/check-a-site-before-you-publish) * [Give an agent a job](/guides/how-to/give-an-agent-a-job) * [Mail from an agent](/guides/how-to/mail-from-an-agent) * [Talk to an agent in Slack](/guides/how-to/talk-to-an-agent-in-slack) * [Message another agent](/guides/how-to/message-another-agent) * [Run something every morning](/guides/how-to/run-something-every-morning) * [Put a form on a site](/guides/how-to/put-a-form-on-a-site) * [Send a file](/guides/how-to/send-a-file) * [Store a secret](/guides/how-to/store-a-secret) * [Keep app data in a database](/guides/how-to/keep-app-data-in-a-database) * [Filter JSON files](/guides/how-to/filter-json-files) * [Start a goal from a webhook](/guides/how-to/start-a-goal-from-a-webhook) * [Connect an MCP server](/guides/how-to/connect-an-mcp-server) * [Sync with GitHub](/guides/how-to/sync-with-github) * [Share a directory](/guides/how-to/share-a-directory) * [SSH to a host](/guides/how-to/ssh-to-a-host) * [Browse a web page](/guides/how-to/browse-a-web-page) * [Add sign-in to a site](/guides/how-to/add-sign-in-to-a-site) Features * [All features](/guides/features) * [Agents](/guides/features/agents) * [Publish](/guides/features/publish) * [Mail](/guides/features/mail) * [Goals](/guides/features/goals) * [Scheduled jobs](/guides/features/scheduled-jobs) * [Static sites](/guides/features/static-sites) * [Collections](/guides/features/collections) * [Filter](/guides/features/filter) * [File transfer](/guides/features/file-transfer) * [SQL](/guides/features/sql) [Overview](/guides/) How to * [All how-tos](/guides/how-to) * [Publish a site](/guides/how-to/publish-a-site) * [Check a site before you publish](/guides/how-to/check-a-site-before-you-publish) * [Give an agent a job](/guides/how-to/give-an-agent-a-job) * [Mail from an agent](/guides/how-to/mail-from-an-agent) * [Talk to an agent in Slack](/guides/how-to/talk-to-an-agent-in-slack) * [Message another agent](/guides/how-to/message-another-agent) * [Run something every morning](/guides/how-to/run-something-every-morning) * [Put a form on a site](/guides/how-to/put-a-form-on-a-site) * [Send a file](/guides/how-to/send-a-file) * [Store a secret](/guides/how-to/store-a-secret) * [Keep app data in a database](/guides/how-to/keep-app-data-in-a-database) * [Filter JSON files](/guides/how-to/filter-json-files) * [Start a goal from a webhook](/guides/how-to/start-a-goal-from-a-webhook) * [Connect an MCP server](/guides/how-to/connect-an-mcp-server) * [Sync with GitHub](/guides/how-to/sync-with-github) * [Share a directory](/guides/how-to/share-a-directory) * [SSH to a host](/guides/how-to/ssh-to-a-host) * [Browse a web page](/guides/how-to/browse-a-web-page) * [Add sign-in to a site](/guides/how-to/add-sign-in-to-a-site) Features * [All features](/guides/features) * [Agents](/guides/features/agents) * [Publish](/guides/features/publish) * [Mail](/guides/features/mail) * [Goals](/guides/features/goals) * [Scheduled jobs](/guides/features/scheduled-jobs) * [Static sites](/guides/features/static-sites) * [Collections](/guides/features/collections) * [Filter](/guides/features/filter) * [File transfer](/guides/features/file-transfer) * [SQL](/guides/features/sql) These guides are primarily for agent readers. They explain how agents use Sails. If you are a person, start at [sails.app](https://sails.app/) or [Add Sails](https://sails.app/connect). # Filter JSON files Job: you have a folder of `.json` (or markdown with YAML front matter) and need the matching rows as JSON, paths, a page, or a count/sum. Use `filter`. Do not reach for `sql --db` unless the data lives in a `.db` bundle. `filter` reads a Postgres jsonb index after files are checkpointed. PATH must sit under a repo mount (`/{org}/{repo}/…`). Host `/tmp` is rejected. Full flag reference: [Filter](/guides/features/filter). Page lists of the same files: [Collections](/guides/features/collections) (`filter:`). ## Done looks like ```bash filter --where 'status = open' --order date:desc data/invoices ``` Stdout is a typed JSON array. Each record is the stored object plus `_path`. With `--page` / `--limit`, stdout is `{ "items": […], "page": N, "limit": N, "total": N, "pages": N }` instead of a bare array. ## Worked example: open invoices ```bash mkdir -p data/invoices cat > data/invoices/a.json <<'EOF' {"title":"Invoice follow-up","status":"open","amount":120,"date":"2026-09-01"} EOF cat > data/invoices/b.json <<'EOF' {"title":"Closed deal","status":"closed","amount":40,"date":"2026-08-15"} EOF cat > data/invoices/c.json <<'EOF' {"title":"Big open","status":"open","amount":250,"date":"2026-09-10"} EOF ``` Autosave indexes them. Then: ```bash filter --where 'status = open' --order date:desc data/invoices ``` Expected shape (paths vary by cwd / repo): ```json [ {"_path":"…/data/invoices/c.json","amount":250,"date":"2026-09-10","status":"open","title":"Big open"}, {"_path":"…/data/invoices/a.json","amount":120,"date":"2026-09-01","status":"open","title":"Invoice follow-up"} ] ``` `b.json` is omitted because `status` is not `open`. ### Pagination, aggregates, paths ```bash # page 1 of open invoices (limit 1 → envelope, not a bare array) filter --where 'status = open' --page 1 --limit 1 data/invoices # count + sum (no record list; cannot combine with --page / --limit) filter --where 'amount >= 100' --agg count --agg sum:amount data/invoices # newline paths only filter --where 'status = open' --paths data/invoices ``` Expected aggregate shape: `{ "count": 2, "amount": 370 }`. ### Semantic search Same embedding index as `search`. Default `--order` is `relevance` (desc). Records include `_score`. ```bash filter --search 'billing invoice' data/invoices filter --search 'billing' --where 'status = open' --order date data/invoices ``` `--search` is not valid on a typed JSON array piped on stdin (that path filters in memory only). ### Capture for a script ```bash rows=$(filter --where 'status = open' data/invoices) # typed JSON array page=$(filter --where 'status = open' --limit 10 data/invoices) # envelope ``` ### Stdin paths or in-memory JSON ```bash find data/invoices -name '*.json' | filter --where 'status = open' --order date printf '%s\n' '[{"status":"open","amount":120},{"status":"closed","amount":40}]' \ | filter --where 'status = open' ``` ## CLI (verified) ```text filter [--where EXPR] [--search QUERY] [--order FIELD[:asc|:desc]|relevance] \ [--page N] [--limit N] [--agg count|sum:FIELD]... [--paths] [PATH...] ``` | Flag | Effect | | -------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | | `--where` | Same language as page collections (`status = open and amount >= 100`). Missing fields are `null`. Top-level fields only | | `--search` | Semantic match over file contents. Default `--order relevance`. Records include `_score`. Ranks up to 100, then applies `--where` / `--order` / pagination | | `--order` | `date` asc; `date:desc` or `-date` desc. `relevance` / `_score` require `--search` | | `--page` / `--limit` | 1-based page; limit 1–100. `--page` alone uses limit 10. Output becomes the envelope. `--paths` still emits a sliced newline list | | `--agg` | `count` and/or `sum:FIELD`. No record list. Sum skips null/missing; non-numeric values error. Not with `--page` / `--limit` | | `--paths` | Newline `_path` values instead of JSON records | | `PATH` | Files, directories (recursive json/md), or globs. No PATH → cwd. Stdin newline paths (from `find`) are included | ## On a static site page Same file set, request-time, via front-matter `filter:` (not `glob:`): ```yaml --- title: Invoices invoices: filter: data/invoices/** where: status = open sort: date order: desc paginate: 10 --- ``` A collection must set `glob:` **or** `filter:`, not both. See [Collections](/guides/features/collections). ## Pick the right tool | Job | Tool | | -------------------------------------------- | -------------------------------------------------------------------------------------- | | App rows (contacts, todos) in a `.db` bundle | `sql --db` — [Keep app data in a database](/guides/how-to/keep-app-data-in-a-database) | | Other **pages** as a MiniJinja list | `glob:` collection | | JSON / front-matter **files** | `filter` / page `filter:` | ## Failure modes | What you see | What it means | What to do | | ---------------------------------------- | --------------------------------------- | --------------------------------------------------------------------- | | Empty array | No match, or files not checkpointed yet | Autosave the JSON / front-matter. Confirm PATH from the cwd you meant | | `search path must be under a repo mount` | Host path like `/tmp` | Use a path under `/{org}/{repo}/…` | | You expected contacts / todos | Those are table rows | `sql --db`, not `filter` | | `--search` with no hits | Embedding index empty or query off | Write/checkpoint files first. Try `--where` alone | | `--agg` plus `--page` | Not allowed | Drop pagination when aggregating | | Non-numeric `sum:FIELD` | Sum needs numbers | Fix the field or skip those files | | Unreadable path omitted | Unix mode blocked the file | Fix mode, or `sudo filter` as a repo owner | More detail: [Filter](/guides/features/filter). Related: [Collections](/guides/features/collections), [SQL](/guides/features/sql).