I’m interested in composable, expressive querying of arbitrary data.

Notion is a big recursive tree with a lot of interesting relationships. I'm always looking for better ways to describe these relationships.

In SQL databases, this happens at the query level with WITH clauses

I think defining and composing virtual edges seems really important. In the SQLite they're expressed partially as a WITH (subquery) that applies the filter condition, plus a JOIN elsewhere that defines the edge. Linking the two together syntactically could improve things.

SQL

In SQL, you can compose relationships in a few different ways:

• To share with the whole database, create a persisted VIEW in the database with CREATE VIEW.

• Pre-define a relationship using a WITH clause at the start of a query as a “common table expression”. Some SQL databases allow a recursive form of these:

  WITH RECURSIVE
    works_for_alice(n) AS (
      VALUES('Alice')
      UNION
      SELECT name FROM org, works_for_alice
       WHERE org.boss=works_for_alice.n
    )
  SELECT avg(height) FROM org
   WHERE org.name IN works_for_alice;
  

• Use a sub-select to embed a query inside a query.

PartiQL

This is Amazon’s SQL dialect for working with nested semi structured data.

PartiQL Tutorial

Notice that there is a 'Susan Smith' tuple in the result, despite the fact that Susan has no project. Susan’s projectName is null. We can obtain this result by combining employees and projects using theLEFT JOIN operator, as follows:

**SELECT** e.id **AS** id, 
       e.name **AS** employeeName, 
       e.title **AS** title, 
       p.name **AS** projectName
**FROM** hr.employeesNest **AS** e **LEFT** **JOIN** e.projects **AS** p **ON** **true**

The semantics of this query can be thought of as

foreach employee tuple e from hr.employeesNest
    if the e.projects is an empty collection then // this part is special about LEFT JOINs
        output e.id AS id, e.name AS employeeName, e.title AS title
        and output a null AS projectName
    else // the following part is identical to plain (inner) JOINs
        foreach project tuple p from e.projects
            output e.id AS id, e.name AS employeeName, e.title AS title