RoamJS is becoming a part of SamePage! To check out the inter-tool network we're building, click here to learn more!

QUERY BUILDER

Introduces new user interfaces for building queries in Roam!

This extension is available in Roam Depot! Install it directly from Roam by navigating to Settings > RoamDepot > Browse. To help test a development version of the extension before it's available in Roam Depot, click here to download.

Query Builder

Introduces new user interfaces for building queries in Roam.

For more information, check out our docs at https://roamjs.com/extensions/query-builder

Table of Contents

Nomenclature

There are some important terms to know and have exact definitions on since they will be used throughout the docs.

  • Page - A Page is anything in Roam that was created with [[brackets]], #hashtag, #[[hashtag with brackets]], or Attribute::. Clicking on these links in your graph takes you to its designated page, each with its own unique title, and they have no parent.
  • Block - A bullet or line of text in Roam. While you can also go to pages that have a zoomed in block view, their content is not unique, and they always have one parent.
  • Node - A superset of Blocks and Pages.

RoamJS Query Builder

Query Builder can be used to create queries via a Query Block, Query Pages, or the Query Drawer.

These queries are far more powerful than vanilla Roam queries, as it taps into Roam's underlying query language surfaced through an approachable UI.

Query Blocks

The above UI is available as a block component. This allows you to create several on a page, wherever on the page you want.

To create one, simply add {{query block}} to any block on the page.

Query Pages

With Query Pages, you designate certain pages in your Roam graph as "views" into your data.

On the Roam Depot Settings page for Query Builder, you should see a setting called Query Pages. You could use this to denote which page titles in your Roam Graph will be used to create query pages. Use the * as a wildcard.

By default, Query Pages is set to be titled with queries/*. This means any page in your graph prefixed with queries/ can be used to save a query. You can denote multiple page title formats.

Example: [[queries/Demo]]

Query Drawer

The above UI is also available as a left hand drawer, accessible from the command palette. This allows you to execute a query no matter where in your graph you are.

To open, enter Open Query Drawer from the Roam Command Palette.

Usage

Create a Query Block, navigate to a Query Page, or open the Query Drawer to begin to building your query. You can use this editor to create and save a query.

There are two important parts to a query: Conditions and Selections.

After specifying conditions and selections, hit the Query button to return results in your graph. These results will always include a text field which will link to the relevant block or page reference. Hitting Query also effectively "Saves" the query to the graph.

The results returned will be organized in a table with sortable and filterable columns. Click on the columns to sort the data and use the filter icon to narrow down the table to your desired results:

Conditions

Conditions specify which blocks you want to return. They determine the rows of the table. The anatomy of a Condition is a triple: source, relationship, & target:

You can use a combination of multiple conditions to select the data you want.

relationships will autocomplete as you type:

Here are all the supported relationships:

  • has title - The source page has the exact text target as a title. If target is equal to {date}, then it matches any Daily Note Page. Supports date NLP to resolve a single date, e.g. {date:today}. The target also supports Regular Expressions by starting and ending the value with a /.
  • references - The source block or page references the target block or page.
  • references title - The source block or page references a page with target as the title. If target is equal to {date}, then it matches any Daily Note Page. Supports date NLP to resolve a single date, e.g. {date:today}. The target also supports Regular Expressions by starting and ending the value with a /.
  • is referenced by - The source block or page is referenced by the target block or page.
  • is in page - The source block is in the target page.
  • is in page with title - The source block is in a page with title target. If target is equal to {date}, then it matches any Daily Note Page. Supports date NLP to resolve a single date, e.g. {date:today}. The target also supports Regular Expressions by starting and ending the value with a /.
  • has attribute - The source block or page has an attribute with value target.
  • has child - The source block or page has the target block as a child.
  • has ancestor - The source block has the target block or page as an ancestor up the outliner tree.
  • has descendent - The source block or page has the target block as a descendant somewhere down the outliner tree
  • with text - The source block or page has the exact text target somewhere in its block text or page title
  • created by - The source block or page was created by the user with a display name of target
  • edited by - The source block or page was last edited by the user with a display name of target
  • with title in text - The source page has the exact text target somewhere in its page title.
  • created before - The source block or page was created before the naturally specified target
  • created after - The source block or page was created after the naturally specified target
  • edited before - The source block or page was edited before the naturally specified target
  • edited after - The source block or page was edited after the naturally specified target
  • titled before - The source page is a DNP that is before the naturally specified target
  • titled after - The source page is a DNP that is after the naturally specified target

Selections

Selections specify what data from the blocks that match your conditions get returned. They determine the columns of the table. By default, the block text or page title is always returned and hyperlinked. Every selection is made up of two parts: the label and the data:

The label, which gets specified after AS, denotes the name of the column that gets used. The data, which gets specified after Select, denotes what kind of data to return.

The following data types are supported:

  • Created Date - The date the block or page was created
  • Created Time - Same as above, but in hh:mm format
  • Edited Date - The date the block or page was edited
  • Edited Time - Same as above, but in hh:mm format
  • Author - The user who created the block or page
  • Last Edited By - The user who created the block or page
  • node:{node} - Returns any intermediary node you defined in one of the conditions. For example:
    • node:page will return the title of a page referenced in a condition.
    • node:placeholder will return the title of a placeholder referenced in a condition.
  • node:{node}:{field} - Specify one of the first six options as the field to return the related metadata for the intermediary node. For example:
    • node:page:Author will return the user who created the page defined in a condition.
    • node:placeholder:Client will return the value of the Client attribute from the placeholder node defined in a condition.
  • node:{node}:/regular_expression/ - returns match according to a regular expression between /'s. For example:
    • node:node:/(\d\d?:\d\d)/ will return time in the format of "hours:minutes" from the main node being queried
    • node:placeholder:/#([^\s]*)/ will return the text after the first hashtag from the placeholder node defined in a condition.
  • node - Edit the column header of the first column
  • Anything else is assumed to be an attribute of the exact text

You can also use the aliases in previous selects to derive values for future columns. The following derived selects are supported:

  • add({alias1}, {alias2}) - Add the values of two columns. Supports adding values to dates. If one of the aliases is today, then today's date will be used.
  • subtract({alias1}, {alias2})- Subtract the values betweenn two columns. Supports adding values to dates. If one of the aliases is today, then today's date will be used.

Manipulating Results

After you fire a query, the results will output in a table view. There are multiple ways to post process these results after they output to the screen.

Sort

Clicking on the table header for a given column will trigger an alphabetical sort. Clicking again will toggle descending order. Clicking once more will toggle the sort off. You could have multiple columns selected for sorting:

Filter

Each column is also filterable. The filter works just like the page and reference filters in native Roam, where you could pick values to include and remove:

View Type

Each column also has a view type. Choosing a view type will change how the cell is displayed in the table. The supported view types are:

  • plain - Outputted as just plain text
  • link - If the column is a block, cells will be outputted as a link to the block. If the column is a page, cells will be outputted as a link to the page.
  • embed - Embeds the contents of the block or page in the cell.

All changes to the outputted results are saved automatically.

Layouts

By default, the query builder will use the Table layout. You can switch to a different layout by hitting the more menu on the top right of the results and clicking on the Layout option.

The following values are also supported:

  • Line - Displays your data as a line chart. You need to have at least two selections for this layout to work, where the first is a selection that returns date values and all subsequent selections return numeric values.
  • Bar - Displays your data as a bar chart. You need to have at least two selections for this layout to work, where the first is a selection that returns date values and all subsequent selections return numeric values.
  • Timeline - Displays your data as an interactive timeline view. You need to have a selection chosen labelled Date that returns date values for this layout to work.

Exporting

Next to the save button is a button that will allow you to export your results. There are currently two formats available to export to:

  • CSV - All the columns in the table will become columns in the CSV
  • Markdown - The columns will become frontmatter data and the children of the block or page will become markdown content.

Styling

Every Query Block or Query Page is rooted with a div that has an id of roamjs-query-page-${uid} where uid is the block reference of the query block or the page reference of the page. You could use this id to style individual queries with affecting other ones.

SmartBlocks Integration

This extension comes with its own SmartBlocks command! The <%QUERYBUILDER%> command will run an existing Query Block or Query Page instance in your graph and return the results as separate blocks. The command takes in two arguments:

  1. The block reference, alias, or page title of the query instance
  2. The format to output each result in. You can use placeholders, like {text} to insert the value from the result in. There's a placeholder available for each Selection label used in the query.

Alias

You can set the alias of a Query Block in the top right corner of the UI.

The end of the title of a Query Page works as well.

Examples

<%QUERYBUILDER:6-qLCJsSb,{text}%>

<%QUERYBUILDER:myQuery,{uid}%>

<%QUERYBUILDER:queries/myQuery%>

Developer API

For developers of other extensions who want to use the queries defined by users, we expose the following API, available on the global window.roamjs.extension.queryBuilder object:

  • listActiveQueries - () => { uid: string }[] Returns an array of blocks or pages where the user has a query defined from query builder.
  • runQuery - (uid: string) => Promise<Result[]> Runs the query defined at the input uid and returns a promise that resolves to the array of results from the user's graphs. Results have the following schema:
    • text - string The page title or block text of the primary node involved in the result.
    • uid - string The reference of the primary node involved in the result.
    • ${string}-uid - string If the users define selections that return intermediary nodes, the reference of those nodes will always end in -uid and will always be of type string.
    • {string} - string | number | Date All other fields returned in the result can be any of the primitive value types.

Demos

General Demo

Demo showing Query Block, Query Pages, Query Drawer, Conditions, Selections and a few example queries.

Direct Video Link

Conversation with Conor White-Sullivan

About Roam Depot, extensions, and a Query Builder demo. Demo starts around 13 minutes.

(in the demo you'll see the block being used, but that can be interchanged with node)

View on Grain

Native Roam Queries

In addition to new RoamJS Query Builder components, this extension enhances Roam's native querying experience by providing features such as an intuitive UI for creating and editing queries, sorting and randomizing query results, and displaying more context in these results.

Creating Native Roam Queries

In a block, type {{qb}}.

Similar to date picker, there will be an overlay that appears next to the QUERY button. After specifying different query components that you're interested in searching, hit save to insert the query syntax into the block.

The overlay is fully keyboard accessible. Each input is focusable and you can tab and shift+tab through them. For the query component dropdown, you could use the following key strokes to navigate:

  • Arrow Up/Arrow Down - Navigate Options
  • Enter - Open Dropdown
  • a - Select 'AND'
  • o - Select 'OR'
  • b - Select 'BETWEEN'
  • t - Select 'TAG'
  • n - Select 'NOT'

On any deletable component, you could hit ctrl+Backspace or cmd+Backspace to delete the icon. Hitting enter on the save button will output the query into the block.

There will also be an edit button rendered on any existing query. Clicking the edit icon will overlay the builder to edit the existing query!

Manipulating Native Roam Queries

The legacy Query Tools extension was merged with this one to bring all native query manipulation features under Query Builder. These features could be configured within the Roam Depot Settings for Query Builder.

  • Default Sort - The default sorting all native queries in your graph should use
  • Sort Blocks - If set to 'True', sort the query results by blocks instead of pages.
  • Context - The default value for Context for all queries. See below.

Direct Link to Video

Sorting

On expanded queries, there will be a sort icon that appears next to the results text. Clicking on the sort icon will make a sort menu visible to the user.

To persist a particular sort on a query, create a block on the page with the text Default Sort. Nest the value under that block.

To configure sorting by blocks on a single query instead of on all queries in your DB, add a block that says Sort Blocks as a child to the specific query.

Here are the options you can sort by:

  • Sort By Page Title - This will sort all the query results in ascending alphabetical order of the page title.
  • Sort By Page Title Descending - This will sort all the query results in descending alphabetical order of the page title.
  • Sort By Word Count - This will sort all the query results in ascending order of the word count.
  • Sort By Word Count Descending - This will sort all the query results in descending alphabetical order of the word count.
  • Sort By Created Date - This will sort all the query results in ascending order that the page was created.
  • Sort By Created Date Descending - This will sort all the query results in descending order that the page was created.
  • Sort By Edited Date - This will sort all the query results in ascending order that the page was last edited.
  • Sort By Edited Date Descending - This will sort all the query results in descending order that the page was last edited.
  • Sort By Daily Note - This will sort all the query results in ascending order by Daily Note, followed by created date of non-daily note pages.
  • Sort By Daily Note Descending - This will sort all the query results in descending order by Daily Note, followed by created date of non-daily note pages.

Randomization

Sometimes we have queries with hundreds of results and want to return a random element from that query. Returning random results from multiple queries could lead to serendipitous connections. To return a random result from a query, add a block that says Random as a child of the query. Nest the value representing the number of random results you'd like returned from the query.

Context

By default, query results only display the most nested block in the result. To display more context in a given query, add a block that says Context as a child block of the query. Set the value to the number of levels you'd like displayed, or Top to display full context, as a child of this Context block.

Aliases

By default, query results display the query logic itself for the label. To display an alias for the given query, add a block that says Alias as a child block of the query, with the value of the alias nested below that.

Sortable Linked References

In your Roam Depot Settings, there is a flag called Sortable Linked References. When enabled, there will appear a sort icon next to the filter icon near the linked references. Clicking on the sort icon will make a sort menu visible to the user.

You could also add a Default Sort attribute with a valid value on the page itself to have a specific sorting for just that page.

Here are the options you can sort by:

  • Sort By Page Title - This will sort all the linked references in ascending alphabetical order of the page title.
  • Sort By Page Title Descending - This will sort all the linked references in descending alphabetical order of the page title.
  • Sort By Created Date - This will sort all the linked references in ascending order that the page was created.
  • Sort By Created Date Descending - This will sort all the linked references in descending order that the page was created.
  • Sort By Daily Note - This will sort all the linked references in ascending order by Daily Note, followed by created date of non-daily note pages.
  • Sort By Daily Note Descending - This will sort all the linked references in descending order by Daily Note, followed by created date of non-daily note pages.

Discourse Graphs

This extension implements the Discourse Graph protocol, developed by Joel Chan. To enable the features associated with this protocol, toggle the Discourse Graphs Enabled switch.

For more about the suite of tools this mode brings, checkout our documentation for how to use this extension at https://oasis-lab.gitbook.io/roamresearch-discourse-graph-extension/.

Contact Joel Chan (joelchan@umd.edu or @JoelChan86 on Twitter or in the #discourse-graph channel on the Academia Roamana Discord) for more details!

Contributors

This extension is brought to you by RoamJS! If you are facing any issues reach out to support@roamjs.com or click on the chat button on the bottom right.

Other Extensions