Nested data in one query — Examples

Nested data in one query

Fetch a row together with its related rows in a single query. A nested shape compiles to a json_agg lateral subquery, so PostgreSQL returns the parent and its children as one JSON structure — no second round trip, no N+1.

multi select User {
  id,
  email,
  articles := (multi select Article { id, title } filter .owner = User.id)
};

compiles to:

SELECT
  u.id AS id,
  u.email AS email,
  (SELECT COALESCE(json_agg(row_to_json(a_articles_sub)), '[]')
     FROM (SELECT a.id AS id, a.title AS title
             FROM "article" a WHERE a.owner = u.id) a_articles_sub) AS articles
FROM "user" u;

• The inner multi select yields a JSON array ([] when empty); drop multi for a single related object instead.
• User.id refers to the outer row — that’s how the subquery correlates children to their parent.

From generated code

Saved as list_users_with_articles.aql, the query decodes straight into nested types — no manual joining, no second query:

TypeScript

const users = await runner.query.listUsersWithArticles();
// users[0].articles is already ListUsersWithArticlesRowArticles[]
for (const u of users) {
  console.log(u.email, u.articles.length);
}

Go

users, err := runner.Query.ListUsersWithArticles(ctx)
// users[0].Articles is []ListUsersWithArticlesRowArticles
for _, u := range users {
    fmt.Println(u.Email, len(u.Articles))
}

See Codegen and Nested shapes.