Read and filter rows

select_rows is the one operation that reads rows: you filter, sort, page, group and join with it, and you store its queries as views.

The examples query a diary of classic films with three tables: film, actor for the cast, and watch with one row per viewing from the last few weeks. The viewing dates count back from the day of the site’s build.

Filter rows, choose columns and sort

Every key of where must match, and a plain value means equal. columns picks the columns of the answer, which always includes path. order_by sorts the rows, and a leading - sorts them in descending order.

select_rows

{
  "table": "film",
  "columns": ["title", "minutes"],
  "where": { "status": "watched" },
  "order_by": "-minutes"
}

Answer:

{
  "table": "main.public.film",
  "total": 6,
  "rows": [
    {
      "path": "datanotes/main/public/film/Charade.md",
      "title": "Charade",
      "minutes": 113
    },
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead",
      "minutes": 96
    },
    {
      "path": "datanotes/main/public/film/The Gold Rush.md",
      "title": "The Gold Rush",
      "minutes": 95
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "minutes": 94
    },
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday",
      "minutes": 92
    },
    {
      "path": "datanotes/main/public/film/Sherlock Jr..md",
      "title": "Sherlock Jr.",
      "minutes": 45
    }
  ]
}

Compare values with operators

A where key takes an object of operators in place of a plain value. These are the operators:

eq and ne: equal and not equal
gt, gte, lt and lte: greater than, at least, less than and at most
in: equal to one of the listed values
contains: contains the text, or has the item in a list
icontains: contains the text, ignoring case and accents
exists: has a value (true) or has none (false)

select_rows

{
  "table": "film",
  "columns": ["title", "minutes"],
  "where": { "minutes": { "gte": 90, "lt": 100 } }
}

Answer:

{
  "table": "main.public.film",
  "total": 4,
  "rows": [
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday",
      "minutes": 92
    },
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead",
      "minutes": 96
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "minutes": 94
    },
    {
      "path": "datanotes/main/public/film/The Gold Rush.md",
      "title": "The Gold Rush",
      "minutes": 95
    }
  ]
}

select_rows

{
  "table": "film",
  "columns": ["title"],
  "where": { "cast": { "contains": "[[Cary Grant]]" } }
}

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    { "path": "datanotes/main/public/film/Charade.md", "title": "Charade" },
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday"
    }
  ]
}

icontains finds Cary Grant from a part of his name in lowercase:

select_rows

{
  "table": "actor",
  "columns": ["name", "born"],
  "where": { "name": { "icontains": "grant" } }
}

Answer:

{
  "table": "main.public.actor",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/actor/Cary Grant.md",
      "name": "Cary Grant",
      "born": "1904-01-18"
    }
  ]
}

exists: false finds the films with no rating:

select_rows

{ "table": "film", "columns": ["title"], "where": { "rating": { "exists": false } } }

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Battleship Potemkin.md",
      "title": "Battleship Potemkin"
    },
    { "path": "datanotes/main/public/film/Metropolis.md", "title": "Metropolis" }
  ]
}

select_rows

{
  "table": "film",
  "columns": ["title", "year"],
  "where": { "year": { "in": [1925, 1927] } }
}

Answer:

{
  "table": "main.public.film",
  "total": 3,
  "rows": [
    {
      "path": "datanotes/main/public/film/Battleship Potemkin.md",
      "title": "Battleship Potemkin",
      "year": 1925
    },
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "title": "Metropolis",
      "year": 1927
    },
    {
      "path": "datanotes/main/public/film/The Gold Rush.md",
      "title": "The Gold Rush",
      "year": 1925
    }
  ]
}

Combine filters with `or`, `and` and `not`

or, and and not combine filters, and you can nest them. The first query finds the films still to watch and the watched films over 100 minutes. The second leaves out every film with Cary Grant.

select_rows

{
  "table": "film",
  "columns": ["title", "status", "minutes"],
  "where": {
    "or": [
      { "status": "to watch" },
      { "and": [{ "status": "watched" }, { "minutes": { "gt": 100 } }] }
    ]
  }
}

Answer:

{
  "table": "main.public.film",
  "total": 3,
  "rows": [
    {
      "path": "datanotes/main/public/film/Battleship Potemkin.md",
      "title": "Battleship Potemkin",
      "status": "to watch",
      "minutes": 75
    },
    {
      "path": "datanotes/main/public/film/Charade.md",
      "title": "Charade",
      "status": "watched",
      "minutes": 113
    },
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "title": "Metropolis",
      "status": "to watch",
      "minutes": 153
    }
  ]
}

select_rows

{
  "table": "film",
  "columns": ["title"],
  "where": { "not": { "cast": { "contains": "[[Cary Grant]]" } } }
}

Answer:

{
  "table": "main.public.film",
  "total": 6,
  "rows": [
    {
      "path": "datanotes/main/public/film/Battleship Potemkin.md",
      "title": "Battleship Potemkin"
    },
    { "path": "datanotes/main/public/film/Metropolis.md", "title": "Metropolis" },
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead"
    },
    { "path": "datanotes/main/public/film/Nosferatu.md", "title": "Nosferatu" },
    { "path": "datanotes/main/public/film/Sherlock Jr..md", "title": "Sherlock Jr." },
    { "path": "datanotes/main/public/film/The Gold Rush.md", "title": "The Gold Rush" }
  ]
}

Filter by relative dates

The engine resolves tokens like {today}, {today-7d} and {now-24h} when the query runs, so a stored query keeps returning current rows. On a date column, a date-time counts by its day, so {now-7d} matches as {today-7d} does. This query finds the viewings of the last seven days.

select_rows

{
  "table": "watch",
  "columns": ["film", "occurred_at", "where", "place"],
  "where": { "occurred_at": { "gte": "{today-7d}" } },
  "order_by": "occurred_at"
}

Answer:

{
  "table": "main.public.watch",
  "total": 3,
  "rows": [
    {
      "path": "datanotes/main/public/watch/2026-10-07T2115.md",
      "film": "[[Charade]]",
      "occurred_at": "2026-10-07T21:15",
      "where": "home",
      "place": "my living room"
    },
    {
      "path": "datanotes/main/public/watch/2026-10-10T2030.md",
      "film": "[[The Gold Rush]]",
      "occurred_at": "2026-10-10T20:30",
      "where": "home",
      "place": "my living room"
    },
    {
      "path": "datanotes/main/public/watch/2026-10-11T2200.md",
      "film": "[[Night of the Living Dead]]",
      "occurred_at": "2026-10-11T22:00",
      "where": "cinema",
      "place": "the film club downtown"
    }
  ]
}

Filter rows by a link

contains matches a list that holds the link, which you write with the note’s name.

select_rows

{
  "table": "film",
  "columns": ["title"],
  "where": { "cast": { "contains": "[[Cary Grant]]" } }
}

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    { "path": "datanotes/main/public/film/Charade.md", "title": "Charade" },
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday"
    }
  ]
}

A dotted key follows the link and filters on a column of the linked row. Here it finds the actors of a film released after 1950:

select_rows

{ "table": "actor", "columns": ["name"], "where": { "films.year": { "gt": 1950 } } }

Answer:

{
  "table": "main.public.actor",
  "total": 2,
  "rows": [
    { "path": "datanotes/main/public/actor/Audrey Hepburn.md", "name": "Audrey Hepburn" },
    { "path": "datanotes/main/public/actor/Cary Grant.md", "name": "Cary Grant" }
  ]
}

film.minutes finds the viewings of films longer than 100 minutes:

select_rows

{
  "table": "watch",
  "columns": ["film", "where"],
  "where": { "film.minutes": { "gt": 100 } }
}

Answer:

{
  "table": "main.public.watch",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/watch/2026-09-26T2130.md",
      "film": "[[Charade]]",
      "where": "home"
    }
  ]
}

Page through the results

limit sets the page size (default 100, at most 1,000) and offset skips rows before the page. total counts every matching row, whatever the page.

select_rows

{
  "table": "watch",
  "columns": ["occurred_at"],
  "order_by": "-occurred_at",
  "limit": 2,
  "offset": 2
}

Answer:

{
  "table": "main.public.watch",
  "total": 7,
  "rows": [
    {
      "path": "datanotes/main/public/watch/2026-10-07T2115.md",
      "occurred_at": "2026-10-07T21:15"
    },
    {
      "path": "datanotes/main/public/watch/2026-10-03T2130.md",
      "occurred_at": "2026-10-03T21:30"
    }
  ]
}

Read the note behind a row

Every row is a note, and you can select and filter these file columns:

file_name: the note’s name
file_link: a link to the note
file_modified: when the file last changed
file_backlink_count: the notes that link to it

include_body adds the note’s text to each row.

select_rows

{
  "table": "film",
  "columns": ["file_name", "file_link", "file_backlink_count"],
  "where": { "title": "His Girl Friday" }
}

Answer:

{
  "table": "main.public.film",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "file_name": "His Girl Friday",
      "file_link": "[[datanotes/main/public/film/His Girl Friday|His Girl Friday]]",
      "file_backlink_count": 2
    }
  ]
}

select_rows

{
  "table": "film",
  "columns": ["title"],
  "where": { "title": "Charade" },
  "include_body": true
}

Answer:

{
  "table": "main.public.film",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/film/Charade.md",
      "title": "Charade",
      "body": "[[Cary Grant]] again, twenty years after [[His Girl Friday]], and just as quick.\n"
    }
  ]
}

Read and filter rows

Filter rows, choose columns and sort

Compare values with operators

Combine filters with or, and and not

Filter by relative dates

Filter rows by a link

Page through the results

Read the note behind a row

Combine filters with `or`, `and` and `not`