MongoDB Aggregation Pipeline to DQL

{ $match: { status: "active", price: { $gt: 50 } } }

SELECT * FROM orders WHERE status = 'active' AND price > 50

-- Range filter
SELECT * FROM products WHERE price BETWEEN 10 AND 100

-- Membership test
SELECT * FROM orders WHERE status IN ('active', 'pending')

-- Pattern match (case-insensitive)
SELECT * FROM users WHERE name ILIKE 'john%'

-- Null / missing checks
SELECT * FROM documents WHERE archived IS NULL
SELECT * FROM documents WHERE tags IS NOT MISSING

{ $project: { name: 1, price: 1 } }

SELECT name, price FROM products

{ $project: { name: 1, discounted: { $multiply: ["$price", 0.9] } } }

SELECT name, price * 0.9 AS discounted FROM products

{ $addFields: { adjusted_price: { $multiply: ["$price", 1.1] } } }

SELECT products.*, price * 1.1 AS adjusted_price FROM products

{ $unset: ["internal_notes", "cost"] }

SELECT products.*, MISSING internal_notes, MISSING cost FROM products

SELECT object_unset(metadata, 'internal', true) AS metadata FROM products

{
  $group: {
    _id: "$category",
    total_sales: { $sum: "$price" },
    avg_price: { $avg: "$price" },
    count: { $sum: 1 }
  }
}

SELECT
  category,
  SUM(price) AS total_sales,
  AVG(price) AS avg_price,
  COUNT(*) AS count
FROM products
GROUP BY category

{ $group: { _id: "$category", count: { $sum: 1 } } },
{ $match: { count: { $gt: 5 } } }

SELECT category, COUNT(*) AS count
FROM products
GROUP BY category
HAVING COUNT(*) > 5

SELECT category, COUNT(DISTINCT supplier_id) AS unique_suppliers
FROM products
GROUP BY category

[
  { $sort: { created_at: -1 } },
  { $skip: 20 },
  { $limit: 10 }
]

SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10
OFFSET 20

{ $count: "total" }

SELECT COUNT(*) AS total FROM products

SELECT COUNT(*) AS total FROM products WHERE category = 'electronics'

{ $unwind: "$tags" }
// then: { $match: { tags: "electronics" } }

-- Find documents where any tag matches
SELECT * FROM products
WHERE ANY tag IN tags SATISFIES tag = 'electronics' END

-- Transform: extract only active line items
SELECT ARRAY item FOR item IN line_items WHEN item.status = 'active' END AS active_items
FROM orders

{ $project: { prices_with_tax: { $map: { input: "$item_prices", as: "p", in: { $multiply: ["$$p", 1.08] } } } } }

SELECT ARRAY price * 1.08 FOR price IN item_prices END AS prices_with_tax
FROM orders

{ $project: { active_items: { $filter: { input: "$items", as: "item", cond: { $eq: ["$$item.status", "active"] } } } } }

SELECT ARRAY item FOR item IN items WHEN item.status = 'active' END AS active_items
FROM orders

{ $project: { first: { $arrayElemAt: ["$items", 0] } } }

SELECT items[0] AS first_item FROM orders

{ $project: { tag_count: { $size: "$tags" } } }

SELECT array_length(tags) AS tag_count FROM products

{ $project: { has_electronics: { $in: ["electronics", "$tags"] } } }

SELECT array_contains(tags, 'electronics') AS has_electronics FROM products

{ $project: { merged: { $mergeObjects: ["$defaults", "$settings"] } } }

SELECT object_concat(defaults, settings) AS merged FROM users

{ $project: { pairs: { $objectToArray: "$doc" } } }

SELECT object_content(doc, 'fields') AS pairs FROM collection1

{ $project: { id_map: { $arrayToObject: { $map: { input: "$catalog", as: "v", in: { k: "$$v._id", v: "$$v.name" } } } } } }

SELECT OBJECT v._id:v.name FOR v IN catalog END AS id_map
FROM product_catalog

{ $project: { tier: { $cond: { if: { $gt: ["$price", 100] }, then: "premium", else: "standard" } } } }

SELECT
  price,
  CASE WHEN price > 100 THEN 'premium' ELSE 'standard' END AS tier
FROM products

SELECT
  price,
  CASE
    WHEN price > 100 THEN 'premium'
    WHEN price > 50  THEN 'mid'
    ELSE 'budget'
  END AS tier
FROM products

{ $project: { display: { $ifNull: ["$nickname", "$name"] } } }

SELECT ifnull(nickname, name) AS display_name FROM users

{ $project: { code: { $switch: { branches: [ { case: { $eq: ["$color","red"] }, then: 1 }, { case: { $eq: ["$color","blue"] }, then: 2 } ], default: 0 } } } }

SELECT
  CASE color
    WHEN 'red'  THEN 1
    WHEN 'blue' THEN 2
    ELSE 0
  END AS color_code
FROM cars

{ $bucket: { groupBy: "$price", boundaries: [0, 25, 75, 200], default: "other" } }

SELECT
  CASE
    WHEN price < 25  THEN 'budget'
    WHEN price < 75  THEN 'mid'
    WHEN price < 200 THEN 'premium'
    ELSE 'other'
  END AS bucket,
  COUNT(*) AS count
FROM products
GROUP BY
  CASE
    WHEN price < 25  THEN 'budget'
    WHEN price < 75  THEN 'mid'
    WHEN price < 200 THEN 'premium'
    ELSE 'other'
  END

-- Parse a date string
SELECT date_cast('2024-01-15', 'YYYY-MM-DD') AS ts FROM system:dual

-- Format a stored timestamp
SELECT date_format(created_at, 'YYYY-MM-DD') AS date_str FROM orders

-- Add 7 days
SELECT date_add(created_at, 'day', 7) AS due_date FROM orders

-- Difference between dates in days
SELECT date_diff(due_date, created_at, 'day') AS days_open FROM tasks

-- Extract the year
SELECT date_part(created_at, 'year') AS yr FROM orders

-- Regex match (case-insensitive flag)
SELECT regexp_like(name, '^[A-Z]') AS starts_capital FROM users

-- Substring
SELECT substr(name, 0, 3) AS prefix FROM users

-- Concatenation
SELECT concat(first_name, ' ', last_name) AS full_name FROM users

-- Case conversion
SELECT lower(name) AS name_lower, upper(name) AS name_upper FROM users

-- Trim whitespace (note backtick quoting)
SELECT `trim`(name) AS name_trimmed FROM users

{ $project: { val_type: { $type: "$value" } } }

SELECT type(value) AS val_type FROM data

{ $project: { price_str: { $toString: "$price" } } }

SELECT cast(price, 'string') AS price_str FROM products

[
  { $match: { status: "active" } },
  { $project: { name: 1, category: 1, price: 1 } },
  { $group: { _id: "$category", avg_price: { $avg: "$price" }, count: { $sum: 1 } } },
  { $sort: { avg_price: -1 } },
  { $limit: 5 }
]

SELECT
  category,
  AVG(price) AS avg_price,
  COUNT(*) AS count
FROM products
WHERE status = 'active'
GROUP BY category
ORDER BY avg_price DESC
LIMIT 5

const { orders, customer } = await ditto.store.transaction(
  async (tx) => {
    const orders = await tx.execute(
      "SELECT * FROM orders WHERE customer_id = :id",
      { id: customerId }
    );
    const customer = await tx.execute(
      "SELECT * FROM customers WHERE _id = :id",
      { id: customerId }
    );
    return { orders: orders.items, customer: customer.items[0] };
  },
  { hint: "join-simulation", isReadOnly: true }
);
// Join in application code
const enriched = orders.map((o) => ({ ...o, customer }));

const facets = await ditto.store.transaction(
  async (tx) => {
    const byCategory = await tx.execute(
      "SELECT category, COUNT(*) AS count FROM products GROUP BY category"
    );
    const byStatus = await tx.execute(
      "SELECT status, COUNT(*) AS count FROM products GROUP BY status"
    );
    return { byCategory: byCategory.items, byStatus: byStatus.items };
  },
  { hint: "facet-simulation", isReadOnly: true }
);

await ditto.store.transaction(
  async (tx) => {
    const summary = await tx.execute(
      "SELECT category, COUNT(*) AS count, AVG(price) AS avg_price FROM products GROUP BY category"
    );
    for (const row of summary.items) {
      await tx.execute(
        "INSERT INTO product_summaries VALUES (:doc) ON ID CONFLICT DO UPDATE",
        { doc: { _id: row.category, count: row.count, avg_price: row.avg_price } }
      );
    }
    return "commit";
  },
  { hint: "aggregate-and-write" }
);