Views
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
Section titled “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
{ "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:
{ "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,andornoton either side: both filters apply
select_rows
{ "table": "vw_recent_watches", "where": { "where": "home" } }Answer:
{ "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:
{ "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
Section titled “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
{ "name": "vw_recent_watches", "query": { "table": "watch", "where": { "occurred_at": { "gte": "{today-30d}" } } }}Refused:
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
{ "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:
{ "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
Section titled “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
{ "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:
{ "ok": true, "op": "create_view", "planned_texts": ["datanotes/metastore.yaml"], "migration_id": "20261012T165228-create_view-create-view-main-public-vw_las"}select_rows
{ "table": "vw_last_watched" }Answer (65 lines)
{ "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
Section titled “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 nonewhere: a filter on every branch, on top of its ownexpand: the links to expand in every branchorder_byandlimit: 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
{ "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:
{ "ok": true, "op": "create_view", "planned_texts": ["datanotes/metastore.yaml"], "migration_id": "20261012T165228-create_view-create-view-main-public-vw_mos"}select_rows
{ "table": "vw_most_linked" }Answer:
{ "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
{ "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:
{ "ok": true, "op": "create_view", "planned_texts": ["datanotes/metastore.yaml"], "migration_id": "20261012T165228-create_view-create-view-main-public-vw_lat"}select_rows
{ "table": "vw_latest_watches" }Answer (64 lines)
{ "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
{ "view": "vw_most_linked" }Answer:
{ "ok": true, "op": "drop_view", "planned_texts": ["datanotes/metastore.yaml"], "migration_id": "20261012T165228-drop_view-drop-view-main-public-vw_most_"}undo_migration
{ "migration_id": "20261012T165228-drop_view-drop-view-main-public-vw_most_" }Answer:
{ "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
Section titled “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
{ "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:
{ "ok": true, "op": "create_view", "planned_texts": ["datanotes/metastore.yaml"], "migration_id": "20261012T165228-create_view-create-view-main-reports-vw_wa"}select_rows
{ "table": "reports.vw_watches_by_place" }Answer:
{ "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.*}
Section titled “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’swatched_ondayvw_film_watches: every viewing of a filmvw_same_status: the other films with the film’s statusvw_films_with: the films of an actor’s note
batch
{ "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:
{ "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
{ "table": "vw_film_watches", "this_note": "datanotes/main/public/film/The Gold Rush.md" }Answer:
{ "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
{ "table": "vw_watches_that_day", "columns": ["film", "occurred_at", "where"], "this_note": "datanotes/main/public/film/The Gold Rush.md"}Answer:
{ "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
{ "table": "vw_same_status", "this_note": "datanotes/main/public/film/Metropolis.md" }Answer:
{ "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
{ "table": "vw_films_with", "this_note": "datanotes/main/public/actor/Cary Grant.md" }Answer:
{ "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
{ "table": "vw_film_watches" }Answer:
{ "table": "main.public.watch", "total": 0, "rows": [], "view": "main.public.vw_film_watches"}See the views in metastore.yaml
Section titled “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:
# Metastore (Datanotes): catalogs, schemas, tables, columns, views.id: 01a13107-a9c5-7c62-b7dc-6307d7d9e4bcconfig: 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