Query model

JSONVault supports three complementary query surfaces:

1. Mongo-like filter objects with operators such as $and, $in, $regex, $all, $elemMatch, $mod, $type, and nested $not.
2. JSONPath expressions compiled via db.compile("$.orders[?(@.total > 1000)]").
3. A SQL helper that covers projections, joins, grouping, HAVING, ordering, and limiting.

Filter operators

await users.find({
  $and: [
    { status: { $in: ["active", "pending"] } },
    { tags: { $all: ["tech", "sale"] } },
    { variants: { $elemMatch: { price: { $gt: 30 } } } },
    { createdAt: { $mod: [2, 0] } },
    { meta: { $type: ["object", "null"] } },
  ],
  status: { $not: { $eq: "archived" } },
});

Operators compose; use nested $not to invert specific comparisons without rewiring the whole filter.

JSONPath

When you need ad-hoc traversal logic, compile a JSONPath string and stream the results:

const query = db.compile("$.orders[?(@.total > 1000 && @.status == 'complete')]");

for await (const order of db.stream(query)) {
  console.log(order.id, order.total);
}

SQL helper

Select

const totals = await db.sql`
  SELECT userId, SUM(total) AS totalSpent
  FROM orders
  WHERE status = 'paid'
  GROUP BY userId
  HAVING totalSpent > 1000
  ORDER BY totalSpent DESC
  LIMIT 10
`;

Join

const rows = await db.sql`
  SELECT orders.id AS orderId,
         orders.total,
         users.email
  FROM orders
  JOIN users ON orders.userId = users._id
  WHERE orders.total > 500
  ORDER BY orderId
`;

JSONPath passthrough

const expensive = await db.sql("$.orders[?(@.total > 1000)]");

SQL feature checklist

• SELECT columns with aliases.
• Aggregates: SUM, AVG, MIN, MAX, COUNT.
• WHERE, BETWEEN, IN, IS NULL, basic boolean logic (AND).
• Single inner JOIN on equality (JOIN users ON orders.userId = users._id).
• GROUP BY, HAVING, ORDER BY, LIMIT.
• Template parameters (${value}) keep user input safe.

Upcoming extensions often involve support for multiple joins, OR conditions in SQL, and automatic pagination.