# Convert a MongoDB query to SQL

Paste a MongoDB find filter or db.collection.find() shell call and get the equivalent SQL condition, WHERE clause, or full SELECT statement.

## Run it

- **CLI:** `gizza tool mongodb-query-to-sql 'db.users.find({ age: { $gte: 21 }, status: "active" })'`
- **Web:** https://gizza.ai/tools/mongodb-query-to-sql/
- **Agents:** machine-readable descriptor (parameters JSON Schema) at https://gizza.ai/tools/mongodb-query-to-sql/tool.json

## Inputs

- `query` — MongoDB query _(field)_
- `output` — Output _(field)_
- `dialect` — SQL dialect _(field)_
- `table` — Table name (SELECT output) _(field)_
- `nested` — Dotted paths _(field)_
- `quote_identifiers` — Quote identifiers _(field)_
- `rename_id` — Rename _id to id _(field)_

## Output

- SQL (text)

## Query parameters

Open the tool pre-filled and auto-run via URL:

- `query` — MongoDB query
- `output` — Output
- `dialect` — SQL dialect
- `table` — Table name (SELECT output)
- `nested` — Dotted paths
- `quote_identifiers` — Quote identifiers
- `rename_id` — Rename _id to id

Example: `https://gizza.ai/tools/mongodb-query-to-sql/?query=db.users.find%28%7B%20age%3A%20%7B%20%24gte%3A%2021%20%7D%2C%20status%3A%20%22active%22%20%7D%29&output=where&dialect=ansi&table=sales.orders&nested=column&quote_identifiers=true&rename_id=true`

---

## What this tool does

Paste a MongoDB filter document or a `db.collection.find(...)` shell query and this tool converts it into deterministic SQL. It can emit a bare boolean condition, a `WHERE` clause, or a full `SELECT` statement with projection, sort, limit, skip, and count handling.

The parser accepts common Mongo shell syntax: unquoted keys, single quotes, trailing commas, comments, regular-expression literals, `ObjectId()`, `ISODate()`, `new Date()`, numeric helpers such as `NumberLong()`, and MongoDB Extended JSON wrappers like `$oid` and `$date`.

## Worked example

Input:

```javascript
db.orders.find(
  { status: { $in: ["paid", "shipped"] }, total: { $gt: 100 } },
  { _id: 0, orderId: 1, total: 1 }
).sort({ total: -1 }).limit(10).skip(20)
```

With **Output** set to `select`, the ANSI SQL output is:

```sql
SELECT "orderId", "total"
FROM "orders"
WHERE "status" IN ('paid', 'shipped') AND "total" > 100
ORDER BY "total" DESC
LIMIT 10 OFFSET 20;
```

## Options

- **Output**: `where` adds the `WHERE` keyword, `condition` returns only the boolean expression, and `select` builds a complete statement.
- **Dialect**: choose ANSI, PostgreSQL, MySQL/MariaDB, or SQL Server for quoting, regex behavior, JSON extraction, and paging syntax.
- **Table name**: used for `select` output when the input is a bare filter document or when you want to override the MongoDB collection name.
- **Dotted paths**: keep `address.city` as one column name, or translate it as a JSON path extraction for the selected dialect.
- **Quote identifiers**: turn this off when you want unquoted column names.
- **Rename _id to id**: useful for schemas migrated from MongoDB where `_id` became a relational `id` column.

## Supported operators and limits

The translator supports `$eq`, `$ne`, `$gt`, `$gte`, `$lt`, `$lte`, `$in`, `$nin`, `$and`, `$or`, `$nor`, field-level `$not`, `$exists`, `$regex`, `$mod`, and `$size` where the SQL dialect has a safe equivalent. Unsupported operators explain why rather than guessing.

Input is limited to **100,000 characters** and nesting is limited to **64 levels**. Aggregation pipelines, writes, `$lookup`, `$group`, `$elemMatch`, `$all`, geo queries, text search, and schema-dependent array rewrites are intentionally not translated because they need collection schema knowledge.

## FAQ

<details>
<summary>Can this convert aggregation pipelines?</summary>

No. A `$match` stage can often be pasted as a normal find filter, but stages such as `$group`, `$lookup`, and `$unwind` require schema and join decisions that are not present in a MongoDB snippet. The tool rejects pipelines instead of inventing a misleading SQL query.

</details>

<details>
<summary>How are dotted fields handled?</summary>

By default `profile.city` is treated as one SQL column name. If your document is stored in a JSON column, set **Dotted paths** to `json`; PostgreSQL uses `->>` with casts, MySQL uses `JSON_UNQUOTE(JSON_EXTRACT(...))`, and SQL Server/ANSI use `JSON_VALUE(...)`.

</details>

<details>
<summary>Does regular expression output work in every SQL dialect?</summary>

PostgreSQL and MySQL have regex operators, so most simple patterns can be emitted there. ANSI SQL and SQL Server do not have a portable regex operator, so only plain anchored patterns can become `LIKE`; complex patterns return an error that suggests switching dialects.

</details>

<details>
<summary>Will the generated SQL be parameterized?</summary>

No. The output is designed to be readable and pasteable. If you use it in application code, replace literal values with your database driver's placeholders and bind parameters before running it against real data.

</details>

## Related tools

- [Parse a Postal Address](https://gizza.ai/tools/address-parse/): Parse a freeform postal address into street, unit, city, region, postcode, and country fields. Local, rule-based, and browser-only.
- [Authorization Header Decoder](https://gizza.ai/tools/authorization-header-decode/): Decode an HTTP Authorization header: Basic to username and password, Bearer token structure, Digest and AWS SigV4 params. Free, in your browser.
- [Basic Auth Header Generator](https://gizza.ai/tools/basic-auth-header-generator/): Generate an HTTP Basic Authorization header from a username and password — base64(user:pass) per RFC 7617. Runs in your browser, nothing is sent, free.
- [Calendar Free/Busy Overlap](https://gizza.ai/tools/calendar-freebusy-overlap/): Paste two .ics calendars and list the time slots where both are free — working hours, minimum meeting length, timezones and DST handled. Free and in-browser.
- [CIDR Calculator](https://gizza.ai/tools/cidr-calculator/): CIDR calculator: get network address, broadcast, netmask, wildcard, usable host range and host count for any IPv4 or IPv6 prefix. Free, private, in-browser.