# Views

> Store a query as a view, change it, join and combine tables in it, put it in a schema, and read the note that shows it.

A view is a `select_rows` query stored under a name in `metastore.yaml`. You read it like a table, narrow it with more filters, and agents read it instead of rebuilding the filter, so the answer follows your definition.

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.

## Store a query as a view

`create_view` stores a query in `metastore.yaml` under a name; give it the `vw_` prefix. You select a view like a table and can narrow it further, and it writes no note.

Agents find a view by its `description` and `aliases`. `labels` are the column headings a client shows when it renders the view as a table in a note; `select_rows` still answers with the column names.

**`create_view`**

```json
{
  "name": "vw_recent_watches",
  "query": {
    "table": "watch",
    "columns": ["film", "occurred_at", "where", "place"],
    "where": { "occurred_at": { "gte": "{today-14d}" } },
    "order_by": "-occurred_at"
  },
  "description": "Films watched in the last two weeks",
  "aliases": ["what I watched lately"],
  "labels": {
    "film": "Film",
    "occurred_at": "Watched",
    "where": "Where",
    "place": "Place"
  }
}
```

Answer:

```json
{
  "ok": true,
  "op": "create_view",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165228-create_view-create-view-main-public-vw_rec"
}
```

A `where` in `select_rows` narrows the view. It combines with the view’s own filter this way:

* **A column the view doesn’t filter**: the engine adds the condition to the view’s filter
* **A column the view filters**: the engine replaces an operator the view already has and adds a new one
* **`or`, `and` or `not` on either side**: both filters apply

**`select_rows`**

```json
{ "table": "vw_recent_watches", "where": { "where": "home" } }
```

Answer:

```json
{
  "table": "main.public.watch",
  "total": 2,
  "rows": [
    {
      "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-07T2115.md",
      "film": "[[Charade]]",
      "occurred_at": "2026-10-07T21:15",
      "where": "home",
      "place": "my living room"
    }
  ],
  "view": "main.public.vw_recent_watches"
}
```

`list_tables` lists the views next to the tables:

**`list_tables`** (no arguments)

Answer:

```json
{
  "views": [
    {
      "name": "vw_recent_watches",
      "catalog": "main",
      "schema": "public",
      "qualified": "main.public.vw_recent_watches",
      "description": "Films watched in the last two weeks",
      "aliases": ["what I watched lately"]
    }
  ]
}
```

## Change an existing view

Once a view exists, `create_view` refuses its name. `replace: true` replaces the whole view, like `CREATE OR REPLACE VIEW` in Structured Query Language (SQL), so pass its `description`, `aliases` and `labels` again.

**`create_view`**

```json
{
  "name": "vw_recent_watches",
  "query": { "table": "watch", "where": { "occurred_at": { "gte": "{today-30d}" } } }
}
```

Refused:

```text
create_view refused — nothing was written:
conflict main.public.vw_recent_watches: main.public.vw_recent_watches already exists (pass replace)
Hint: The name is taken: choose another one, or pass replace: true to replace a view.
Docs: https://datanotes.dev/guides/database-objects/catalogs-and-schemas.md#place-tables-in-catalogs-and-schemas (offline: read_docs("guides/database-objects/catalogs-and-schemas#place-tables-in-catalogs-and-schemas"))
```

**`create_view`**

```json
{
  "name": "vw_recent_watches",
  "replace": true,
  "query": {
    "table": "watch",
    "columns": ["film", "occurred_at", "where", "place"],
    "where": { "occurred_at": { "gte": "{today-30d}" } },
    "order_by": "-occurred_at"
  },
  "description": "Films watched in the last month",
  "aliases": ["what I watched lately"],
  "labels": {
    "film": "Film",
    "occurred_at": "Watched",
    "where": "Where",
    "place": "Place"
  }
}
```

Answer:

```json
{
  "ok": true,
  "op": "create_view",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165228-create_view-create-view-main-public-vw_rec-2"
}
```

## Join and expand rows in a view

A view stores any query. This one keeps the last three films watched (`order_by`, `limit`) and puts the notes of each film’s cast inline (`expand`). A `left` join attaches the viewing of the `watched_on` day, so a film with no viewing logged that day gets an empty list.

**`create_view`**

```json
{
  "name": "vw_last_watched",
  "query": {
    "table": "film",
    "columns": ["title", "watched_on", "cast"],
    "where": { "status": "watched" },
    "expand": ["cast"],
    "join": {
      "table": "watch",
      "on": { "watched_on": "date(occurred_at)" },
      "as": "viewing",
      "type": "left",
      "columns": ["where"]
    },
    "order_by": "-watched_on",
    "limit": 3
  }
}
```

Answer:

```json
{
  "ok": true,
  "op": "create_view",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165228-create_view-create-view-main-public-vw_las"
}
```

**`select_rows`**

```json
{ "table": "vw_last_watched" }
```

Answer (65 lines)

```json
{
  "table": "main.public.film",
  "total": 6,
  "rows": [
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead",
      "watched_on": "2026-10-11",
      "cast": [
        {
          "path": "datanotes/main/public/actor/Duane Jones.md",
          "table": "main.public.actor",
          "id": "01a13107-a9fb-7cf1-82ee-cb87c1f34b01",
          "name": "Duane Jones",
          "born": "1937-04-02"
        }
      ],
      "viewing": [
        { "path": "datanotes/main/public/watch/2026-10-11T2200.md", "where": "cinema" }
      ]
    },
    {
      "path": "datanotes/main/public/film/The Gold Rush.md",
      "title": "The Gold Rush",
      "watched_on": "2026-10-10",
      "cast": [
        {
          "path": "datanotes/main/public/actor/Charlie Chaplin.md",
          "table": "main.public.actor",
          "id": "01a13107-a9dc-7912-ab26-924514ce8028",
          "name": "Charlie Chaplin",
          "born": "1889-04-16"
        }
      ],
      "viewing": [
        { "path": "datanotes/main/public/watch/2026-10-10T2030.md", "where": "home" }
      ]
    },
    {
      "path": "datanotes/main/public/film/Charade.md",
      "title": "Charade",
      "watched_on": "2026-10-07",
      "cast": [
        {
          "path": "datanotes/main/public/actor/Cary Grant.md",
          "table": "main.public.actor",
          "id": "01a13107-a9e8-73ff-90b0-479b0b06d4bd",
          "name": "Cary Grant",
          "born": "1904-01-18"
        },
        {
          "path": "datanotes/main/public/actor/Audrey Hepburn.md",
          "table": "main.public.actor",
          "id": "01a13107-a9f5-71dc-b490-6d3b52baee4c",
          "name": "Audrey Hepburn",
          "born": "1929-05-04"
        }
      ],
      "viewing": [
        { "path": "datanotes/main/public/watch/2026-10-07T2115.md", "where": "home" }
      ]
    }
  ],
  "view": "main.public.vw_last_watched"
}
```

## Combine several tables in one view

A view with `union` stacks the rows of several queries, like SQL’s `UNION ALL`, on one table or on several. It tags each row with `_source`, and each branch takes `table`, `columns` and `where`. Next to `union`, these keys apply to the whole view:

* **`columns`**: the columns of every branch that gives none
* **`where`**: a filter on every branch, on top of its own
* **`expand`**: the links to expand in every branch
* **`order_by`** and **`limit`**: the order and the number of the stacked rows

This view stacks the films made before 1960 and the actors. Its `where` keeps the notes that at least two other notes link to: the films and actors your diary mentions most.

**`create_view`**

```json
{
  "name": "vw_most_linked",
  "query": {
    "union": [
      {
        "table": "film",
        "columns": ["title", "year"],
        "where": { "year": { "lt": 1960 } }
      },
      { "table": "actor" }
    ],
    "columns": ["name"],
    "where": { "file_backlink_count": { "gte": 2 } },
    "order_by": "path"
  }
}
```

Answer:

```json
{
  "ok": true,
  "op": "create_view",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165228-create_view-create-view-main-public-vw_mos"
}
```

**`select_rows`**

```json
{ "table": "vw_most_linked" }
```

Answer:

```json
{
  "table": "union",
  "total": 3,
  "rows": [
    {
      "path": "datanotes/main/public/actor/Cary Grant.md",
      "name": "Cary Grant",
      "_source": "actor"
    },
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday",
      "year": 1940,
      "_source": "film"
    },
    {
      "path": "datanotes/main/public/film/The Gold Rush.md",
      "title": "The Gold Rush",
      "year": 1925,
      "_source": "film"
    }
  ],
  "view": "main.public.vw_most_linked"
}
```

The branches can read the same table. This view stacks every trip to the cinema and the last week’s viewings at home. It keeps the three latest and puts each film’s note inline:

**`create_view`**

```json
{
  "name": "vw_latest_watches",
  "query": {
    "union": [
      { "table": "watch", "where": { "where": "cinema" } },
      {
        "table": "watch",
        "where": { "where": "home", "occurred_at": { "gte": "{today-7d}" } }
      }
    ],
    "columns": ["film", "occurred_at", "where"],
    "expand": ["film"],
    "order_by": "-occurred_at",
    "limit": 3
  }
}
```

Answer:

```json
{
  "ok": true,
  "op": "create_view",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165228-create_view-create-view-main-public-vw_lat"
}
```

**`select_rows`**

```json
{ "table": "vw_latest_watches" }
```

Answer (64 lines)

```json
{
  "table": "union",
  "total": 4,
  "rows": [
    {
      "path": "datanotes/main/public/watch/2026-10-11T2200.md",
      "film": {
        "path": "datanotes/main/public/film/Night of the Living Dead.md",
        "table": "main.public.film",
        "id": "01a13107-aa49-7c38-aa69-043d7b25a9e5",
        "title": "Night of the Living Dead",
        "year": 1968,
        "minutes": 96,
        "status": "watched",
        "rating": 4,
        "watched_on": "2026-10-11",
        "director": "George A. Romero",
        "cast": ["[[Duane Jones]]"]
      },
      "occurred_at": "2026-10-11T22:00",
      "where": "cinema",
      "_source": "watch"
    },
    {
      "path": "datanotes/main/public/watch/2026-10-10T2030.md",
      "film": {
        "path": "datanotes/main/public/film/The Gold Rush.md",
        "table": "main.public.film",
        "id": "01a13107-aa27-7458-a451-994c50a478c1",
        "title": "The Gold Rush",
        "year": 1925,
        "minutes": 95,
        "status": "watched",
        "rating": 5,
        "watched_on": "2026-10-10",
        "director": "Charlie Chaplin",
        "cast": ["[[Charlie Chaplin]]"]
      },
      "occurred_at": "2026-10-10T20:30",
      "where": "home",
      "_source": "watch"
    },
    {
      "path": "datanotes/main/public/watch/2026-10-07T2115.md",
      "film": {
        "path": "datanotes/main/public/film/Charade.md",
        "table": "main.public.film",
        "id": "01a13107-aa40-770e-be1b-50aeba75062e",
        "title": "Charade",
        "year": 1963,
        "minutes": 113,
        "status": "watched",
        "rating": 4,
        "watched_on": "2026-10-07",
        "director": "Stanley Donen",
        "cast": ["[[Cary Grant]]", "[[Audrey Hepburn]]"]
      },
      "occurred_at": "2026-10-07T21:15",
      "where": "home",
      "_source": "watch"
    }
  ],
  "view": "main.public.vw_latest_watches"
}
```

`drop_view` removes a view, and `undo_migration` puts it back, as it does for every schema change:

**`drop_view`**

```json
{ "view": "vw_most_linked" }
```

Answer:

```json
{
  "ok": true,
  "op": "drop_view",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165228-drop_view-drop-view-main-public-vw_most_"
}
```

**`undo_migration`**

```json
{ "migration_id": "20261012T165228-drop_view-drop-view-main-public-vw_most_" }
```

Answer:

```json
{
  "ok": true,
  "op": "undo_migration",
  "undoes": "20261012T165228-drop_view-drop-view-main-public-vw_most_",
  "migration_id": "20261012T165228-undo_migration-undo-20261012t165228-drop_view",
  "conflicts": [],
  "failed": []
}
```

## Put a view in a schema of its own

`catalog` and `schema` place a view the way they place a table, and default to the database’s default catalog and schema. A schema can hold views only. The query can read the tables of any schema, named here in full.

**`create_view`**

```json
{
  "catalog": "main",
  "schema": "reports",
  "name": "vw_watches_by_place",
  "query": {
    "table": "main.public.watch",
    "group_by": "where",
    "aggregates": { "viewings": "count" }
  },
  "description": "Viewings at the cinema, at home and outdoors"
}
```

Answer:

```json
{
  "ok": true,
  "op": "create_view",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165228-create_view-create-view-main-reports-vw_wa"
}
```

**`select_rows`**

```json
{ "table": "reports.vw_watches_by_place" }
```

Answer:

```json
{
  "table": "main.public.watch",
  "total": 3,
  "rows": [
    { "where": "cinema", "viewings": 2 },
    { "where": "home", "viewings": 4 },
    { "where": "outdoor screening", "viewings": 1 }
  ],
  "view": "main.reports.vw_watches_by_place"
}
```

## Read the note that shows a view with `{this.*}`

A view can read the note that shows it, so one view serves every note of a kind. These are the tokens:

* **`{this.<property>}`**: a front matter key of that note, such as `{this.watched_on}`; `{this.watched_on+1d}` is the day after and `{this.watched_on-1w}` a week before
* **`{this.link}`**: a link to the note
* **`{this.name}`** and **`{this.path}`**: the note’s name and vault path

The engine stores the tokens as written. It fills them in when `select_rows` names the note with `this_note`, which a client passes when it shows the view inside a note. This `batch` creates four views:

* **`vw_watches_that_day`**: the viewings on a film’s `watched_on` day
* **`vw_film_watches`**: every viewing of a film
* **`vw_same_status`**: the other films with the film’s status
* **`vw_films_with`**: the films of an actor’s note

**`batch`**

```json
{
  "summary": "views for film and actor notes",
  "operations": [
    {
      "op": "create_view",
      "name": "vw_watches_that_day",
      "query": {
        "table": "watch",
        "where": {
          "occurred_at": { "gte": "{this.watched_on}", "lt": "{this.watched_on+1d}" }
        },
        "order_by": "occurred_at"
      }
    },
    {
      "op": "create_view",
      "name": "vw_film_watches",
      "query": {
        "table": "watch",
        "columns": ["occurred_at", "where"],
        "where": { "film": "{this.link}" }
      }
    },
    {
      "op": "create_view",
      "name": "vw_same_status",
      "query": {
        "table": "film",
        "columns": ["title"],
        "where": { "status": "{this.status}", "path": { "ne": "{this.path}" } }
      }
    },
    {
      "op": "create_view",
      "name": "vw_films_with",
      "query": {
        "table": "film",
        "columns": ["title", "year"],
        "where": { "cast": { "contains": "[[{this.name}]]" } }
      }
    }
  ]
}
```

Answer:

```json
{
  "ok": true,
  "migration_id": "20261012T165228-batch-views-for-film-and-actor-notes",
  "steps": [
    { "op": "create_view", "ok": true },
    { "op": "create_view", "ok": true },
    { "op": "create_view", "ok": true },
    { "op": "create_view", "ok": true }
  ]
}
```

`this_note` takes the vault path of the note the result is for, and the tokens read that note. These are the viewings of The Gold Rush, as its note shows them:

**`select_rows`**

```json
{ "table": "vw_film_watches", "this_note": "datanotes/main/public/film/The Gold Rush.md" }
```

Answer:

```json
{
  "table": "main.public.watch",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/watch/2026-09-23T2100.md",
      "occurred_at": "2026-09-23T21:00",
      "where": "home"
    },
    {
      "path": "datanotes/main/public/watch/2026-10-10T2030.md",
      "occurred_at": "2026-10-10T20:30",
      "where": "home"
    }
  ],
  "view": "main.public.vw_film_watches"
}
```

For the same note, `{this.watched_on}` is the day you last watched The Gold Rush:

**`select_rows`**

```json
{
  "table": "vw_watches_that_day",
  "columns": ["film", "occurred_at", "where"],
  "this_note": "datanotes/main/public/film/The Gold Rush.md"
}
```

Answer:

```json
{
  "table": "main.public.watch",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/watch/2026-10-10T2030.md",
      "film": "[[The Gold Rush]]",
      "occurred_at": "2026-10-10T20:30",
      "where": "home"
    }
  ],
  "view": "main.public.vw_watches_that_day"
}
```

For Metropolis’s note, `{this.status}` is its `status` and `{this.path}` leaves Metropolis itself out. This is the other film still to watch:

**`select_rows`**

```json
{ "table": "vw_same_status", "this_note": "datanotes/main/public/film/Metropolis.md" }
```

Answer:

```json
{
  "table": "main.public.film",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/film/Battleship Potemkin.md",
      "title": "Battleship Potemkin"
    }
  ],
  "view": "main.public.vw_same_status"
}
```

For Cary Grant’s note, `[[{this.name}]]` is a link to him:

**`select_rows`**

```json
{ "table": "vw_films_with", "this_note": "datanotes/main/public/actor/Cary Grant.md" }
```

Answer:

```json
{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    { "path": "datanotes/main/public/film/Charade.md", "title": "Charade", "year": 1963 },
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday",
      "year": 1940
    }
  ],
  "view": "main.public.vw_films_with"
}
```

Without `this_note`, a token stays as written and matches no row, whatever its operator. That holds for `film: "{this.link}"` here, for a `ne` like the `path` one, and for `within`.

**`select_rows`**

```json
{ "table": "vw_film_watches" }
```

Answer:

```json
{
  "table": "main.public.watch",
  "total": 0,
  "rows": [],
  "view": "main.public.vw_film_watches"
}
```

## See the views in `metastore.yaml`

The engine wrote this page’s views under `views:` in their schema, next to the tables. It keeps each query as you gave it, with its relative dates and `{this.*}` tokens.

`datanotes/metastore.yaml` now reads:

```yaml
# Metastore (Datanotes): catalogs, schemas, tables, columns, views.
id: 01a13107-a9c5-7c62-b7dc-6307d7d9e4bc
config:
  history: true


catalogs:
  main:
    schemas:
      public:
        tables:
          actor:
            description: Actors of the films I watch
            options:
              filename: "{name}"
              archive: _archive
            columns:
              - name: id
                type: string
                required: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
                unique: true
                identity: uuid
                dense: true
              - name: name
                type: string
                required: true
                description: The actor's name, as credited
              - name: born
                type: date
                description: The actor's date of birth
          film:
            description: Classic films I watched or want to watch
            options:
              filename: "{title}"
              archive: _archive
            columns:
              - name: id
                type: string
                required: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
                unique: true
                identity: uuid
                dense: true
              - name: title
                type: string
                required: true
                description: The film's title, as on its poster
                unique: true
              - name: year
                type: integer
                description: Year the film first came out
              - name: minutes
                type: integer
                min: 1
                description: Running time in minutes
              - name: status
                type: enum
                values:
                  - to watch
                  - watched
                description: Where the film is on my list
                ordered: true
              - name: rating
                type: integer
                min: 1
                max: 5
                description: My rating, from 1 (poor) to 5 (a masterpiece)
              - name: watched_on
                type: date
                description: The day I last watched it
              - name: director
                type: string
                description: Who directed the film
              - name: cast
                type: list
                items: link
                ref: main.public.actor
                description: The actors in the film
          watch:
            description: Each time I watch a film
            options:
              filename: "{occurred_at:YYYY-MM-DD[T]HHmm}"
              archive: _archive
            columns:
              - name: id
                type: string
                required: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
                unique: true
                identity: uuid
                dense: true
              - name: film
                type: link
                required: true
                ref: main.public.film
                description: The film I watched
              - name: occurred_at
                type: datetime
                required: true
                description: When the film started
              - name: where
                type: enum
                values:
                  - cinema
                  - home
                  - outdoor screening
                description: The kind of place I watched it in
              - name: place
                type: string
                description: The cinema, park or room, in my words
              - name: with
                type: string
                description: Who watched it with me
        views:
          vw_recent_watches:
            description: Films watched in the last month
            aliases:
              - what I watched lately
            labels:
              film: Film
              occurred_at: Watched
              where: Where
              place: Place
            query:
              table: watch
              columns:
                - film
                - occurred_at
                - where
                - place
              where:
                occurred_at:
                  gte: "{today-30d}"
              order_by: -occurred_at
          vw_last_watched:
            query:
              table: film
              columns:
                - title
                - watched_on
                - cast
              where:
                status: watched
              expand:
                - cast
              join:
                table: watch
                on:
                  watched_on: date(occurred_at)
                as: viewing
                type: left
                columns:
                  - where
              order_by: -watched_on
              limit: 3
          vw_most_linked:
            query:
              union:
                - table: film
                  columns:
                    - title
                    - year
                  where:
                    year:
                      lt: 1960
                - table: actor
              columns:
                - name
              where:
                file_backlink_count:
                  gte: 2
              order_by: path
          vw_latest_watches:
            query:
              union:
                - table: watch
                  where:
                    where: cinema
                - table: watch
                  where:
                    where: home
                    occurred_at:
                      gte: "{today-7d}"
              columns:
                - film
                - occurred_at
                - where
              expand:
                - film
              order_by: -occurred_at
              limit: 3
          vw_watches_that_day:
            query:
              table: watch
              where:
                occurred_at:
                  gte: "{this.watched_on}"
                  lt: "{this.watched_on+1d}"
              order_by: occurred_at
          vw_film_watches:
            query:
              table: watch
              columns:
                - occurred_at
                - where
              where:
                film: "{this.link}"
          vw_same_status:
            query:
              table: film
              columns:
                - title
              where:
                status: "{this.status}"
                path:
                  ne: "{this.path}"
          vw_films_with:
            query:
              table: film
              columns:
                - title
                - year
              where:
                cast:
                  contains: "[[{this.name}]]"
      reports:
        views:
          vw_watches_by_place:
            description: Viewings at the cinema, at home and outdoors
            query:
              table: main.public.watch
              group_by: where
              aggregates:
                viewings: count
```