Joins and aggregates

select_rows groups rows and computes totals with group_by and aggregates, and joins tables on links or on matching values. The examples query a diary of classic films with its cast and viewings.

Group rows and compute totals

group_by groups the rows, and aggregates computes one value per group. These are the aggregates:

• count: the rows in the group
• count:col: the rows where the column has a value
• sum:col, avg:col, min:col and max:col: the total, average, smallest and largest value of the column

select_rows

{
  "table": "film",
  "group_by": "status",
  "aggregates": {
    "films": "count",
    "rated": "count:rating",
    "minutes": "sum:minutes",
    "average": "avg:minutes",
    "longest": "max:minutes"
  }
}

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "status": "to watch",
      "films": 2,
      "rated": 0,
      "minutes": 228,
      "average": 114,
      "longest": 153
    },
    {
      "status": "watched",
      "films": 6,
      "rated": 6,
      "minutes": 535,
      "average": 89.166667,
      "longest": 113
    }
  ]
}

A link column makes one group per linked note. These are the viewings per film:

select_rows

{ "table": "watch", "group_by": "film", "aggregates": { "viewings": "count" } }

Answer:

{
  "table": "main.public.watch",
  "total": 6,
  "rows": [
    { "film": "[[Charade]]", "viewings": 1 },
    { "film": "[[His Girl Friday]]", "viewings": 1 },
    { "film": "[[Night of the Living Dead]]", "viewings": 1 },
    { "film": "[[Nosferatu]]", "viewings": 1 },
    { "film": "[[Sherlock Jr.]]", "viewings": 1 },
    { "film": "[[The Gold Rush]]", "viewings": 2 }
  ]
}

date(col) groups a date-time column by day. These are the viewings per day over the last week:

select_rows

{
  "table": "watch",
  "group_by": "date(occurred_at)",
  "aggregates": { "viewings": "count" },
  "where": { "occurred_at": { "gte": "{today-7d}" } }
}

Answer:

{
  "table": "main.public.watch",
  "total": 3,
  "rows": [
    { "date(occurred_at)": "2026-10-07", "viewings": 1 },
    { "date(occurred_at)": "2026-10-10", "viewings": 1 },
    { "date(occurred_at)": "2026-10-11", "viewings": 1 }
  ]
}

Join rows on a computed key

join attaches to each row the rows of another table whose values match, and on maps a column of this table to a column of the other. date(col) matches a date-time by its day, optionally shifted by a number of days. Here each film watched in the last three days gets the viewing of its watched_on day. The viewing tells you where you saw the film and who came along.

select_rows

{
  "table": "film",
  "columns": ["title", "watched_on"],
  "where": { "watched_on": { "gte": "{today-3d}" } },
  "join": {
    "table": "watch",
    "on": { "watched_on": "date(occurred_at)" },
    "as": "viewing",
    "type": "inner",
    "columns": ["where", "with"]
  }
}

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead",
      "watched_on": "2026-10-11",
      "viewing": [
        {
          "path": "datanotes/main/public/watch/2026-10-11T2200.md",
          "where": "cinema",
          "with": "Oscar"
        }
      ]
    },
    {
      "path": "datanotes/main/public/film/The Gold Rush.md",
      "title": "The Gold Rush",
      "watched_on": "2026-10-10",
      "viewing": [
        {
          "path": "datanotes/main/public/watch/2026-10-10T2030.md",
          "where": "home",
          "with": "my sister"
        }
      ]
    }
  ]
}

Join two tables on matching values

join attaches the rows of another table whose columns match. Here each film gets its viewings: the viewing’s film link matches the film’s own link, file_link.

select_rows

{
  "table": "film",
  "columns": ["title"],
  "join": {
    "table": "watch",
    "on": { "file_link": "film" },
    "as": "viewings",
    "type": "left",
    "columns": ["where", "occurred_at"]
  }
}

Answer:

{
  "table": "main.public.film",
  "total": 3,
  "rows": [
    {
      "path": "datanotes/main/public/film/Charade.md",
      "title": "Charade",
      "viewings": [
        {
          "path": "datanotes/main/public/watch/2026-09-26T2130.md",
          "where": "home",
          "occurred_at": "2026-09-26T21:30"
        }
      ]
    },
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday",
      "viewings": [
        {
          "path": "datanotes/main/public/watch/2026-03-14T2030.md",
          "where": "cinema",
          "occurred_at": "2026-03-14T20:30"
        }
      ]
    },
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead",
      "viewings": []
    }
  ]
}

Count rows across a list of links

group_by on a list column counts each item once, so a film with two actors counts for both, and Cary Grant’s group holds two films. Night of the Living Dead has no cast here, so it falls in the null group.

select_rows

{
  "table": "film",
  "group_by": "cast",
  "aggregates": { "films": "count", "minutes": "sum:minutes" }
}

Answer:

{
  "table": "main.public.film",
  "total": 4,
  "rows": [
    { "cast": "[[Audrey Hepburn]]", "films": 1, "minutes": 113 },
    { "cast": "[[Cary Grant]]", "films": 2, "minutes": 205 },
    { "cast": "[[Rosalind Russell]]", "films": 1, "minutes": 92 },
    { "cast": null, "films": 1, "minutes": 96 }
  ]
}