Lookup and subqueries

Another collection's documents, attached to a row or used as a filter.

lookup attaches the matching documents of another collection to each row, and in (get …) keeps the rows whose value another collection's rows hold. A SQL JOIN on a key is one or the other (From SQL).

Run in the shell, answers and all; the timings are from one run.

fenec> create collection customers (name text, country text);
collection `customers` created
fenec> create collection orders (customer int @hash, total int, status text);
collection `orders` created
fenec> put customers [{name: "Ada", country: "UK"}, {name: "Bo", country: "TR"}, {name: "Cem", country: "TR"}];
3 records (112.38µs)
fenec> put orders [{customer: 1, total: 1200, status: "paid"}, {customer: 1, total: 800, status: "open"}, {customer: 1, total: 300, status: "paid"}, {customer: 2, total: 450, status: "paid"}];
4 records (116.75µs)
fenec> get customers select name lookup orders on customer select total, status order total desc limit 2
name  orders.total  orders.status
----  ------------  -------------
Ada   1200          paid
Ada   800           open
Bo    450           paid
Cem   -             -
(4 rows, 198.08µs)
fenec> get customers select name lookup orders on customer required select total where status = "open"
name  orders.total
----  ------------
Ada   800
(1 rows, 184.96µs)
fenec> get orders select customer, total where customer in (get customers select id where country = "TR")
customer  total
--------  -----
2         450
(1 rows, 176.00µs)

The limit after lookup counts children per parent: Ada keeps her two largest orders, Bo his one, and Cem, who has none, keeps his row. The shell prints the children as joined rows; over JSON they nest under orders. required keeps only the parents with a match, and the last line is a filter by another collection's rows.

The lookup clause

lookup attaches matching documents from another collection to each row — reviews to their products, say. A limit after it counts children per parent.

get products limit 20 lookup reviews on product_id order created desc limit 3

import { connect } from '@fenecdb/web/client'

await db.from('products')
  .limit(20)
  .lookup(
    'reviews',
    { on: 'product_id', order: [['created', 'desc']], limit: 3 }
  )
  .rows();

from fenecdb import Client

(db.collection("products")
    .limit(20)
    .lookup("reviews", on="product_id", order=[("created", "desc")], limit=3)
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("products").
	Limit(20).
	Lookup(
		"reviews",
		fenecdb.On("product_id"),
		fenecdb.Sort("created", "desc"),
		fenecdb.Limit(3),
	).
	Rows(ctx)

using FenecDb;

await db.From("products")
    .Limit(20)
    .Lookup(
        "reviews",
        on: "product_id",
        order: [new SortKey("created", "desc")],
        limit: 3
    )
    .RowsAsync();

import FenecDB

try await db.from("products")
    .limit(20)
    .lookup(
        "reviews",
        on: "product_id",
        order: [SortKey("created", "desc")],
        limit: 3
    )
    .rows()

import com.fenecdb.*

db.from("products")
    .limit(20)
    .lookup(
        "reviews",
        on = "product_id",
        order = listOf(SortKey("created", "desc")),
        limit = 3
    )
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('products')
    .limit(20)
    .lookup(
        'reviews',
        on: 'product_id',
        order: [SortKey('created', 'desc')],
        limit: 3
    )
    .rows();

sends

get products limit $1 lookup reviews on product_id order created desc limit $2
-- [20, 3]

Clauses after lookup apply to the child collection; clauses before it apply to the parent.
The child's key needs id, a @hash index, or an index over several fields it leads. A key of several fields is written on (org, order) = (org, id).
required keeps only parents that have a match.
A level's list may aggregate — lookup orders on shop select count(*) as n, sum(total) as revenue — each parent's children folded, one row a parent; and a get whose list aggregates takes a lookup off its groups. See A level that folds.

Chains, keys over several fields, folded levels, required, flat rows and costs

get products lookup reviews on product_id
get products limit 20 lookup reviews on product_id order created desc limit 3
get products select name where price >= 10000
  lookup reviews on product_id select body where stars >= 4 limit 3
get products lookup tags on sku = sku

import { connect } from '@fenecdb/web/client'

await db.from('products')
  .lookup('reviews', { on: 'product_id' })
  .rows();

await db.from('products')
  .limit(20)
  .lookup(
    'reviews',
    { on: 'product_id', order: [['created', 'desc']], limit: 3 }
  )
  .rows();

await db.from('products')
  .select('name')
  .where('price', '>=', 10000)
  .lookup(
    'reviews',
    { on: 'product_id', select: ['body'], where: { stars: { gte: 4 } }, limit: 3 }
  )
  .rows();

await db.from('products')
  .lookup('tags', { on: 'sku', parentKey: 'sku' })
  .rows();

from fenecdb import Client

db.collection("products").lookup("reviews", on="product_id").rows()

(db.collection("products")
    .limit(20)
    .lookup("reviews", on="product_id", order=[("created", "desc")], limit=3)
    .rows())

(db.collection("products")
    .select("name")
    .where("price", ">=", 10000)
    .lookup(
        "reviews",
        on="product_id",
        select=["body"],
        where={"stars": {"gte": 4}},
        limit=3
    )
    .rows())

(db.collection("products")
    .lookup("tags", on="sku", parent_key="sku")
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("products").
	Lookup("reviews", fenecdb.On("product_id")).
	Rows(ctx)

db.From("products").
	Limit(20).
	Lookup(
		"reviews",
		fenecdb.On("product_id"),
		fenecdb.Sort("created", "desc"),
		fenecdb.Limit(3),
	).
	Rows(ctx)

db.From("products").
	Select("name").
	Where("price", ">=", 10000).
	Lookup(
		"reviews",
		fenecdb.On("product_id"),
		fenecdb.Select("body"),
		fenecdb.Where(fenecdb.Cmp("stars", "gte", 4)),
		fenecdb.Limit(3),
	).
	Rows(ctx)

db.From("products").
	Lookup("tags", fenecdb.On("sku"), fenecdb.ParentKey("sku")).
	Rows(ctx)

using FenecDb;

await db.From("products")
    .Lookup("reviews", on: "product_id")
    .RowsAsync();

await db.From("products")
    .Limit(20)
    .Lookup(
        "reviews",
        on: "product_id",
        order: [new SortKey("created", "desc")],
        limit: 3
    )
    .RowsAsync();

await db.From("products")
    .Select("name")
    .Where("price", ">=", 10000)
    .Lookup(
        "reviews",
        on: "product_id",
        select: ["body"],
        where: Cond.Cmp("stars", "gte", 4),
        limit: 3
    )
    .RowsAsync();

await db.From("products")
    .Lookup("tags", on: "sku", parentKey: "sku")
    .RowsAsync();

import FenecDB

try await db.from("products").lookup("reviews", on: "product_id").rows()

try await db.from("products")
    .limit(20)
    .lookup(
        "reviews",
        on: "product_id",
        order: [SortKey("created", "desc")],
        limit: 3
    )
    .rows()

try await db.from("products")
    .select("name")
    .where("price", ">=", 10000)
    .lookup(
        "reviews",
        on: "product_id",
        select: ["body"],
        where: .cmp("stars", "gte", 4),
        limit: 3
    )
    .rows()

try await db.from("products")
    .lookup("tags", on: "sku", parentKey: "sku")
    .rows()

import com.fenecdb.*

db.from("products").lookup("reviews", on = "product_id").rows()

db.from("products")
    .limit(20)
    .lookup(
        "reviews",
        on = "product_id",
        order = listOf(SortKey("created", "desc")),
        limit = 3
    )
    .rows()

db.from("products")
    .select("name")
    .where("price", ">=", 10000)
    .lookup(
        "reviews",
        on = "product_id",
        select = listOf("body"),
        where = Cond.cmp("stars", "gte", 4),
        limit = 3
    )
    .rows()

db.from("products").lookup("tags", on = "sku", parentKey = "sku").rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('products').lookup('reviews', on: 'product_id').rows();

await db.from('products')
    .limit(20)
    .lookup(
        'reviews',
        on: 'product_id',
        order: [SortKey('created', 'desc')],
        limit: 3
    )
    .rows();

await db.from('products')
    .select(['name'])
    .where('price', '>=', 10000)
    .lookup(
        'reviews',
        on: 'product_id',
        select: ['body'],
        where: Cond.cmp('stars', 'gte', 4),
        limit: 3
    )
    .rows();

await db.from('products')
    .lookup('tags', on: 'sku', parentKey: 'sku')
    .rows();

sends

get products lookup reviews on product_id
get products limit $1 lookup reviews on product_id order created desc limit $2
-- [20, 3]
get products select name where price >= $1 lookup reviews on product_id select body where stars >= $2 limit $3
-- [10000, 4, 3]
get products lookup tags on sku = sku

lookup attaches the matching documents of another collection to the row they belong to. on names the child's field; the parent's key is id unless = <parent> says otherwise, which is the foreign-key-to-primary-key case spelled out.

It is terminal: everything before it binds to the driving collection, everything after it to the looked-up one. That is the whole scoping rule, and it is why there are no qualified names in the language — where on either side means what it has always meant. The price is that a parent clause cannot follow a child one, which reads the way the query runs anyway.

get shops limit 20
  lookup orders on shop_id limit 3
  lookup lines on order_id limit 5

import { connect } from '@fenecdb/web/client'

await db.from('shops')
  .limit(20)
  .lookup('orders', { on: 'shop_id', limit: 3 })
  .lookup('lines', { on: 'order_id', limit: 5 })
  .rows();

from fenecdb import Client

(db.collection("shops")
    .limit(20)
    .lookup("orders", on="shop_id", limit=3)
    .lookup("lines", on="order_id", limit=5)
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("shops").
	Limit(20).
	Lookup("orders", fenecdb.On("shop_id"), fenecdb.Limit(3)).
	Lookup("lines", fenecdb.On("order_id"), fenecdb.Limit(5)).
	Rows(ctx)

using FenecDb;

await db.From("shops")
    .Limit(20)
    .Lookup("orders", on: "shop_id", limit: 3)
    .Lookup("lines", on: "order_id", limit: 5)
    .RowsAsync();

import FenecDB

try await db.from("shops")
    .limit(20)
    .lookup("orders", on: "shop_id", limit: 3)
    .lookup("lines", on: "order_id", limit: 5)
    .rows()

import com.fenecdb.*

db.from("shops")
    .limit(20)
    .lookup("orders", on = "shop_id", limit = 3)
    .lookup("lines", on = "order_id", limit = 5)
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('shops')
    .limit(20)
    .lookup('orders', on: 'shop_id', limit: 3)
    .lookup('lines', on: 'order_id', limit: 5)
    .rows();

sends

get shops limit $1 lookup orders on shop_id limit $2 lookup lines on order_id limit $3
-- [20, 3, 5]

A second lookup chains onto the first: its clauses bind to its collection, and on <child> = <parent> names a field of the level immediately above. Position keeps scoping the query one level deeper, so exactly one collection is in scope at any point and it is the last one named — which is what keeps qualified names out of the language at any depth. Up to 8 levels; deeper is an error, not a silent truncation.

limit after lookup counts children per parent. This is the clause's reason to exist. A join's limit counts pairs, so a single product with 56 374 reviews consumes the whole page and the other nineteen never appear; asking a join for three reviews each needs a window function. Here twenty products with three reviews apiece is one query with a bounded answer.

The child key must be id or carry @hash. Refusing an unindexed one is the same rule near and match follow: the clause is one bucket probe per parent, and quietly becoming a full scan of the child collection instead — 12.98 ms against 0.092 ms on the same 200 000 rows — is not a thing a query should do without saying so.

A key over several fields

create collection lines (org int, order int, line int, qty int, @sorted(org, order, line))

get orders where org = $1 limit 20
  lookup lines on (org, order) = (org, id) order line asc limit 5

import { connect } from '@fenecdb/web/client'

await db.from('orders')
  .where('org', 7)
  .limit(20)
  .lookup(
    'lines',
    { on: ['org', 'order'], parentKey: ['org', 'id'], order: 'line', limit: 5 }
  )
  .rows();

from fenecdb import Client

(db.collection("orders")
    .where("org", 7)
    .limit(20)
    .lookup(
        "lines",
        on=["org", "order"],
        parent_key=["org", "id"],
        order="line",
        limit=5
    )
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("orders").
	Where("org", "=", 7).
	Limit(20).
	Lookup(
		"lines",
		fenecdb.On("org", "order"),
		fenecdb.ParentKey("org", "id"),
		fenecdb.Sort("line", "asc"),
		fenecdb.Limit(5),
	).
	Rows(ctx)

using FenecDb;

await db.From("orders")
    .Where("org", 7)
    .Limit(20)
    .Lookup(
        "lines",
        on: new[] { "org", "order" },
        parentKey: new[] { "org", "id" },
        order: [new SortKey("line")],
        limit: 5
    )
    .RowsAsync();

import FenecDB

try await db.from("orders")
    .where("org", 7)
    .limit(20)
    .lookup(
        "lines",
        on: ["org", "order"],
        parentKey: ["org", "id"],
        order: ["line"],
        limit: 5
    )
    .rows()

import com.fenecdb.*

db.from("orders")
    .where("org", 7)
    .limit(20)
    .lookup(
        "lines",
        on = listOf("org", "order"),
        parentKey = listOf("org", "id"),
        order = listOf(SortKey("line")),
        limit = 5
    )
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('orders')
    .where('org', 7)
    .limit(20)
    .lookup(
        'lines',
        on: ['org', 'order'],
        parentKey: ['org', 'id'],
        order: [SortKey('line')],
        limit: 5
    )
    .rows();

sends

get orders where org = $1 limit $2 lookup lines on (org, order) = (org, id) order line asc limit $3
-- [7, 20, 5]

Where one field does not say which children are a row's — a tenant's rows by (org, user), an order's lines in a file every tenant's orders share, a child keyed by two fields of its parent — on pairs a field of each side for each part, in order: (org, order) = (org, id) is lines.org = orders.org and lines.order = orders.id. The positional rule is the one above: the names on the left are the looked-up collection's, those on the right the level immediately above's, id among them. A null on either side matches nothing, as for a single key.

The child side is answered by an index over several fields whose first parts are the child's fields, in any order: @sorted(org, order, line) or @unique(order, org) answer on (org, order) = …. A parent's values, each made its child field's type as a single key's is, laid end to end as the index lays a row's, are the start of its children's keys, so its children are one run of the index, found by two searches. No such index is refused, naming the one to declare — needs an index whose first fields are those (declare it with @sorted(org, order)) — for the reason an unindexed single key is. A child's id cannot be one of several parts (it names one row alone), and the two sides name as many fields.

Where the level's order is the index's next parts — in its directions or every one reversed, a part the key fixes ordering nothing — each parent's run is walked in that order and stops at the page: "the five latest lines of each order" reads five lines an order, where a bucket is read whole and sorted. Any other order is read and sorted per parent, as for a single key, its ties in ascending id either way. A single field leading an index over several is answered the same way: with no @hash of its own, or where the level's order walks the index past it — lookup reviews on product_id order created desc limit 3 over @sorted(product_id, created desc).

required takes either side here too: where a part is paired with the parent's id and the child filter holds a @hash equality, the children it names map back to their parents by that part, each held to the rest of the key by its own parts against its parent's — the same bytes the probe from the parent would find.

A level that folds

get shops limit 20 lookup orders on shop select count(*) as n, sum(total) as revenue
get couriers limit 20 lookup deliveries on courier
  select count(*) as n, avg(km) as km order at desc limit 50
get shops lookup lines on shop select product, sum(qty) as sold group product
  order sold desc limit 3
get orders select courier, count(*) as n where at >= $1 group courier
  order n desc limit 10 lookup couriers on user = courier select name

import { connect, expr } from '@fenecdb/web/client'

await db.from('shops')
  .limit(20)
  .lookup(
    'orders',
    { on: 'shop', select: [expr('count(*)').as('n'), expr('sum(total)').as('revenue')] }
  )
  .rows();

await db.from('couriers')
  .limit(20)
  .lookup(
    'deliveries',
    { on: 'courier', select: [expr('count(*)').as('n'), expr('avg(km)').as('km')], order: [['at', 'desc']], limit: 50 }
  )
  .rows();

await db.from('shops')
  .lookup(
    'lines',
    { on: 'shop', select: ['product', expr('sum(qty)').as('sold')], group: ['product'], order: [['sold', 'desc']], limit: 3 }
  )
  .rows();

await db.from('orders')
  .select('courier', expr('count(*)').as('n'))
  .where('at', '>=', '2026-10-01')
  .group('courier')
  .order('n', 'desc')
  .limit(10)
  .lookup('couriers', { on: 'user', parentKey: 'courier', select: ['name'] })
  .rows();

from fenecdb import Client, expr

(db.collection("shops")
    .limit(20)
    .lookup(
        "orders",
        on="shop",
        select=[expr("count(*)").as_("n"), expr("sum(total)").as_("revenue")]
    )
    .rows())

(db.collection("couriers")
    .limit(20)
    .lookup(
        "deliveries",
        on="courier",
        select=[expr("count(*)").as_("n"), expr("avg(km)").as_("km")],
        order=[("at", "desc")],
        limit=50
    )
    .rows())

(db.collection("shops")
    .lookup(
        "lines",
        on="shop",
        select=["product", expr("sum(qty)").as_("sold")],
        group=["product"],
        order=[("sold", "desc")],
        limit=3
    )
    .rows())

(db.collection("orders")
    .select("courier", expr("count(*)").as_("n"))
    .where("at", ">=", "2026-10-01")
    .group("courier")
    .order("n", "desc")
    .limit(10)
    .lookup("couriers", on="user", parent_key="courier", select=["name"])
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("shops").
	Limit(20).
	Lookup(
		"orders",
		fenecdb.On("shop"),
		fenecdb.Select(fenecdb.Expr("count(*)").As("n"), fenecdb.Expr("sum(total)").As("revenue")),
	).
	Rows(ctx)

db.From("couriers").
	Limit(20).
	Lookup(
		"deliveries",
		fenecdb.On("courier"),
		fenecdb.Select(fenecdb.Expr("count(*)").As("n"), fenecdb.Expr("avg(km)").As("km")),
		fenecdb.Sort("at", "desc"),
		fenecdb.Limit(50),
	).
	Rows(ctx)

db.From("shops").
	Lookup(
		"lines",
		fenecdb.On("shop"),
		fenecdb.Select("product", fenecdb.Expr("sum(qty)").As("sold")),
		fenecdb.Group("product"),
		fenecdb.Sort("sold", "desc"),
		fenecdb.Limit(3),
	).
	Rows(ctx)

db.From("orders").
	Select("courier", fenecdb.Expr("count(*)").As("n")).
	Where("at", ">=", "2026-10-01").
	Group("courier").
	Order("n", "desc").
	Limit(10).
	Lookup(
		"couriers",
		fenecdb.On("user"),
		fenecdb.ParentKey("courier"),
		fenecdb.Select("name"),
	).
	Rows(ctx)

using FenecDb;

await db.From("shops")
    .Limit(20)
    .Lookup(
        "orders",
        on: "shop",
        select: [Computed.Expr("count(*)").As("n"), Computed.Expr("sum(total)").As("revenue")]
    )
    .RowsAsync();

await db.From("couriers")
    .Limit(20)
    .Lookup(
        "deliveries",
        on: "courier",
        select: [Computed.Expr("count(*)").As("n"), Computed.Expr("avg(km)").As("km")],
        order: [new SortKey("at", "desc")],
        limit: 50
    )
    .RowsAsync();

await db.From("shops")
    .Lookup(
        "lines",
        on: "shop",
        select: ["product", Computed.Expr("sum(qty)").As("sold")],
        group: ["product"],
        order: [new SortKey("sold", "desc")],
        limit: 3
    )
    .RowsAsync();

await db.From("orders")
    .Select("courier", Computed.Expr("count(*)").As("n"))
    .Where("at", ">=", "2026-10-01")
    .Group("courier")
    .Order("n", "desc")
    .Limit(10)
    .Lookup("couriers", on: "user", parentKey: "courier", select: ["name"])
    .RowsAsync();

import FenecDB

try await db.from("shops")
    .limit(20)
    .lookup(
        "orders",
        on: "shop",
        select: [.expr("count(*)").as("n"), .expr("sum(total)").as("revenue")]
    )
    .rows()

try await db.from("couriers")
    .limit(20)
    .lookup(
        "deliveries",
        on: "courier",
        select: [.expr("count(*)").as("n"), .expr("avg(km)").as("km")],
        order: [SortKey("at", "desc")],
        limit: 50
    )
    .rows()

try await db.from("shops")
    .lookup(
        "lines",
        on: "shop",
        select: ["product", .expr("sum(qty)").as("sold")],
        group: ["product"],
        order: [SortKey("sold", "desc")],
        limit: 3
    )
    .rows()

try await db.from("orders")
    .select("courier", .expr("count(*)").as("n"))
    .where("at", ">=", "2026-10-01")
    .group("courier")
    .order("n", "desc")
    .limit(10)
    .lookup("couriers", on: "user", parentKey: "courier", select: ["name"])
    .rows()

import com.fenecdb.*

db.from("shops")
    .limit(20)
    .lookup(
        "orders",
        on = "shop",
        select = listOf(Computed.expr("count(*)").alias("n"), Computed.expr("sum(total)").alias("revenue"))
    )
    .rows()

db.from("couriers")
    .limit(20)
    .lookup(
        "deliveries",
        on = "courier",
        select = listOf(Computed.expr("count(*)").alias("n"), Computed.expr("avg(km)").alias("km")),
        order = listOf(SortKey("at", "desc")),
        limit = 50
    )
    .rows()

db.from("shops")
    .lookup(
        "lines",
        on = "shop",
        select = listOf("product", Computed.expr("sum(qty)").alias("sold")),
        group = listOf("product"),
        order = listOf(SortKey("sold", "desc")),
        limit = 3
    )
    .rows()

db.from("orders")
    .select("courier", Computed.expr("count(*)").alias("n"))
    .where("at", ">=", "2026-10-01")
    .group("courier")
    .order("n", "desc")
    .limit(10)
    .lookup(
        "couriers",
        on = "user",
        parentKey = "courier",
        select = listOf("name")
    )
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('shops')
    .limit(20)
    .lookup(
        'orders',
        on: 'shop',
        select: [Computed.expr('count(*)').as('n'), Computed.expr('sum(total)').as('revenue')]
    )
    .rows();

await db.from('couriers')
    .limit(20)
    .lookup(
        'deliveries',
        on: 'courier',
        select: [Computed.expr('count(*)').as('n'), Computed.expr('avg(km)').as('km')],
        order: [SortKey('at', 'desc')],
        limit: 50
    )
    .rows();

await db.from('shops')
    .lookup(
        'lines',
        on: 'shop',
        select: ['product', Computed.expr('sum(qty)').as('sold')],
        group: ['product'],
        order: [SortKey('sold', 'desc')],
        limit: 3
    )
    .rows();

await db.from('orders')
    .select(['courier', Computed.expr('count(*)').as('n')])
    .where('at', '>=', '2026-10-01')
    .group('courier')
    .order('n', 'desc')
    .limit(10)
    .lookup('couriers', on: 'user', parentKey: 'courier', select: ['name'])
    .rows();

sends

get shops limit $1 lookup orders on shop select count(*) as n, sum(total) as revenue
-- [20]
get couriers limit $1 lookup deliveries on courier select count(*) as n, avg(km) as km order at desc limit $2
-- [20, 50]
get shops lookup lines on shop select product, sum(qty) as sold group product order sold desc limit $1
-- [3]
get orders select courier, count(*) as n where at >= $1 group courier order n desc limit $2 lookup couriers on user = courier select name
-- ["2026-10-01", 10]

A level's list may aggregate, as a get's does, by the same rules: each parent's children — probed once, as any level's are, held to its where — folded into one row, or with group into one row a group, ordered and paged a parent at a time. A level is the get its children would be with the parent's key in its where, and its clauses mean what they mean there: ungrouped, order, limit and offset pick the children folded — a courier's latest 50, walked off @sorted(courier, at desc) as far as the page and no further — and grouped they order and page the groups, a shop's three best sellers, a window function's question in SQL. A parent with no child gets the one row every ungrouped list answers, count 0 and a sum null, and no group.

A get whose list aggregates takes a lookup too: it hangs off the rows the list answers, its groups, and on child = parent names a column of the list — the busiest ten couriers with their names, above, where the list named the courier. The same holds below a level that folds. The positional rule does not move: the names on the left are the looked-up collection's, those on the right the level immediately above's, and that level's rows are its groups.

What stays out is a list of two collections: grouping parents by a field of theirs and folding their children's — revenue by the shops' city — would put a parent's name and a child's beside each other in one list, which is the qualified name the scoping keeps out. Fold the level a parent at a time and group the parents in the client, or keep the field on the child.

required on a level that folds asks of its children, as on any level: an ungrouped list answers a row for every parent, so asked of the rows it would keep them all, and "couriers who delivered, with their counts" is the question. A required level below one that folds drops its groups. Over 2 000 couriers and 200 000 deliveries, a page of the 20 busiest with the count and mean of each one's latest 50 takes 53 µs in process, against 81 for the 50 attached and folded by the client; every delivery of each folded, 3.5 to 4.1 ms against 6.4 to 6.5; the ten busiest with their names off a week's groups 8.3 ms, as the groups and a query for the names apart do, less the round trip between them (make analytics-bench).

get products limit 20 lookup reviews on product_id required where stars = 5
get products count lookup reviews on product_id required where stars = 5

import { connect } from '@fenecdb/web/client'

await db.from('products')
  .limit(20)
  .lookup('reviews', { on: 'product_id', required: true, where: { stars: 5 } })
  .rows();

await db.from('products')
  .lookup('reviews', { on: 'product_id', required: true, where: { stars: 5 } })
  .count();

from fenecdb import Client

(db.collection("products")
    .limit(20)
    .lookup("reviews", on="product_id", required=True, where={"stars": 5})
    .rows())

(db.collection("products")
    .lookup("reviews", on="product_id", required=True, where={"stars": 5})
    .count())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("products").
	Limit(20).
	Lookup(
		"reviews",
		fenecdb.On("product_id"),
		fenecdb.Required(),
		fenecdb.Where(fenecdb.Fields("stars", 5)),
	).
	Rows(ctx)

db.From("products").
	Lookup(
		"reviews",
		fenecdb.On("product_id"),
		fenecdb.Required(),
		fenecdb.Where(fenecdb.Fields("stars", 5)),
	).
	Count(ctx)

using FenecDb;

await db.From("products")
    .Limit(20)
    .Lookup(
        "reviews",
        on: "product_id",
        required: true,
        where: Cond.Cmp("stars", "=", 5)
    )
    .RowsAsync();

await db.From("products")
    .Lookup(
        "reviews",
        on: "product_id",
        required: true,
        where: Cond.Cmp("stars", "=", 5)
    )
    .CountAsync();

import FenecDB

try await db.from("products")
    .limit(20)
    .lookup("reviews", on: "product_id", where: ["stars": 5], required: true)
    .rows()

try await db.from("products")
    .lookup("reviews", on: "product_id", where: ["stars": 5], required: true)
    .count()

import com.fenecdb.*

db.from("products")
    .limit(20)
    .lookup(
        "reviews",
        on = "product_id",
        required = true,
        where = mapOf("stars" to 5)
    )
    .rows()

db.from("products")
    .lookup(
        "reviews",
        on = "product_id",
        required = true,
        where = mapOf("stars" to 5)
    )
    .count()

import 'package:fenecdb/fenecdb.dart';

await db.from('products')
    .limit(20)
    .lookup('reviews', on: 'product_id', required: true, where: {'stars': 5})
    .rows();

await db.from('products')
    .lookup('reviews', on: 'product_id', required: true, where: {'stars': 5})
    .count();

sends

get products limit $1 lookup reviews on product_id required where stars = $2
-- [20, 5]
get products count lookup reviews on product_id required where stars = $1
-- [5]

required drops a parent that no child matches — "products that have a five-star review" rather than "products, with their five-star reviews". It is tested before offset and limit, so a page of twenty comes back with twenty parents that have children rather than whatever survived; a child offset that walks past every match does not undo it, because the parent has matches all the same. It is the one shape where count combines with lookup: nothing is being attached, so counting the parents that have a match is a question with an answer.

required is a pass, and it is not cheap. It is answered from whichever side is smaller: walking the candidate parents and probing each, or — when the child filter carries a @hash equality and the parent's key is id — reading the children that equality names and taking their parents. Both counts that decide are exact, so the choice is not a guess; what neither plan escapes, once the page is put in order or a facet counts every parent, is that one full pass has to happen. With no order the page is the first parents kept, and walking the parents stops once it holds them. Over 20 000 products and 200 000 reviews, for a page of twenty:

`required`, child filter on a `@hash` field	12.07 ms
the same, walking the parents instead	15.63 ms
`required`, no child filter, no `order`	0.036 ms
`count … required`	4.50 ms
a `bool @hash` field on the parent	0.02 ms

Ask it occasionally and required is the right tool. Ask it on every page load and the answer belongs in a field on the parent, maintained on write — which is the same trade the rest of this database makes.

In a chain, required is a statement about its own level: it drops rows of the level immediately above it and stops there. Requiring lines drops the orders that have none; the shops keep their rows, with an empty group, unless the orders level says required as well. Said at every level it composes into "shops with an order that has a line", and the existence check walks the whole way down — a row is only kept if it has a descendant that is itself kept. Nothing acts at a distance, and the flag means the same thing wherever it is written.

lookup combines with neither near, match nor rerank: each ranks one collection's documents, and a score spanning a parent and its children has no defensible meaning. count is refused as well unless the clause is required, since otherwise it collapses the very rows the children would hang from.

order inside a lookup is bounded in advance: the clause carries its own limit, so only offset + limit children can ever be emitted and the rest are never put in order. On a parent with 56 374 children, order created desc limit 3 costs 3.71 ms rather than 7.52 ms. What remains is reading one key per child, which no ordering can avoid — you cannot know the first three without having seen all of them. The query's own order works the same way.

Over JSON the children are nested under the collection's name, and a chain nests all the way down. Flattened into rows, for a caller that wants a table, they arrive the way a join presents them — the same data; a chain widens once per level, one row per root-to-leaf path, and a level that ran out fills its own columns and every column below it with nulls. A parent with no matches keeps its row either way: the page is the parents. What the flat shape cannot show is the per-level limit, which is exactly why the nesting is what the other transports carry.

What it buys, measured (5 000 products, 100 007 reviews, twenty parents with three children each): the clause is 22.7 µs in process and 0.188 ms over HTTP, against 0.332 ms for the same work as a /batch of twenty-one queries merged on the client.

The larger gain is that there is one way to write it. The same question in SQLite, over the same data: a correlated subquery takes 0.053 ms, a window function with the restriction pushed inside the partition 0.331 ms, and the same window function with the restriction left outside 81.34 ms — a factor of 1 500 between three spellings of one question, with nothing in the query text to tell them apart.

A second level is where batching stops being an answer. One level of N+1 fits in a single round trip, because every query in it can be written from the page's ids; the level below cannot, because its keys are in rows that have not come back yet — so the client pays two round trips whatever it does. Over 2 000 shops, 20 000 orders and 200 000 lines, for a page of 20 × 3 × 5: 49.1 µs in process against 139.8 µs for the same page as 81 separate queries, and 0.204 ms for the one HTTP request against 0.415 ms for two /batch trips — measured on loopback, where the extra trip costs almost nothing and still doubles it.

Subqueries

in (get …) filters by rows of another collection: the reverse of lookup. The inner get runs once, before the query, and the one column it selects is the list.

get orders where customer in (get customers select id where country = "TR")

It answers as its list written out. One bucket per value over a @hash field or id, a scan otherwise — the in [..] you would have written.
The inner get selects exactly one column. A field, or one aggregate; no lookup and no count.
It goes wherever a filter goes: get, count, aggregates, a near's or a match's filter, a lookup level's, set … where and del … where.
At most 100 000 distinct values and 4 levels of nesting; past either the query is an error, never a set cut short. A value held by many rows counts once.

Why an inner get is uncorrelated, and its cost

get orders where status = "open" and customer in (get customers select id where tier >= $1)
get orders where not customer in (get customers select id where country = "TR") count
del sessions where user in (get users select name where banned = true)
get customers limit 20 lookup orders on customer
  where id in (get returns select order_id) limit 3
get orders where customer in (get customers select id
  where tier in (get tiers select n where active = true))

It is uncorrelated: the inner get reads nothing of the row being tested, so it runs once, with the same parameters as the query around it — $1 inside is the query's $1. Its column is a field, or one aggregate (get orders select max(total)); a null in it is left out of the list, since it would otherwise find the rows whose field is null, and not … in would lose every row. not is FenecQL's own not, written before: not customer in (get …); customer not in is not FenecQL.

The values become the list an in [..] is answered with, and the same rule holds: a list reaches the @hash index only when every value is one the field's type can express, or the whole list goes to the scan. A long list is looked up rather than walked — a scan of 200 000 rows by an unindexed field against the 2 000 codes an inner get handed it took 2 302 ms compared a value at a time and takes 16. The bound is on the values the list holds, not on the rows that hold them: a funnel's 125 000 buy events from 937 users is a list of 937. Past 100 000 rows the values are told apart, and the inner get read until they number more than 100 000; with a limit, group or ranking it is read one row past the bound, and a limit inside is the query's own cut, and answers. explain names what each inner get found.

Over HTTP a scoped token's rules hold the inner get as they hold any get: the list holds only rows the token may read, so nothing of the others reaches it through what the outer query finds, and a column its rules show on some rows alone is listed from those rows (Fields a read shows). A token's rule may hold one too, held to 1 000 values and answered before each statement (Scoped access). The JavaScript builder takes a query as an in's value: { customer: { in: db.from('customers').select('id').where({ country: 'TR' }) } }. A subscription's shape takes a list only — its stream follows its own collection's writes, and an inner set would not follow the other's.

What it costs, measured (make subquery-bench, 20 000 customers and 200 000 orders, the median of 21). The orders of one country's 2 000 customers by the @hash on customer: 0.83 ms, against 0.72 ms with the 2 000 ids written out; by an unindexed code, 16.1 ms against 15.9. The customers with a rare order — the inner get reading a bucket — 0.20 ms against 0.065 written out and 0.44 for lookup orders on customer required where status = "rare", the same question asked from the other side; with a filter on the customers as well, 0.56 against lookup's 0.95. Where the inner get scans 200 000 orders it is that scan, 10.4 ms, and lookup … required probes each customer's orders instead, 32.5. The difference to the list written out is the inner get: a subquery is one round trip where the list is two.