Docs Menu
Docs Home
/
Relational Migrator
/
Code Generation
/
Query Converter

Convert Queries

On this page

  • About this Task
  • Steps
  • Examples
  • Next Steps
  • Learn More

You can convert embedded application and reporting SQL queries to MongoDB syntax using the query converter. Copy and paste your queries into the query converter to update them to work with MongoDB and your migrated schema.

About this Task

  • The query converter uses AI technology which may not be able to convert long or complex queries, triggers, packages, or stored procedures. Some queries may not be converted correctly while others may not be converted at all. For more information, see AI and Data Usage Information.

  • The query converter uses the relational schema, the MongoDB schema, and the mapping rules in your current project to determine how the queries should be converted. Conversions may fail or be incorrect if the queries reference tables that are not in your relational schema or if they are not mapped to MongoDB collections.

  • Converted queries, triggers, views, packages, and stored procedures are saved in your project and persist through project import and exports.

  • SQL queries are limited to 40,000 text characters.

  • Always review and test the code generated by query converter before deploying it in a production environment.

  • You can view the history of previous conversions in the left-hand Query Converter pane. Each conversion has an icon indicating the result of the conversion. If an object does not have an icon next to it, a conversion has not been attempted yet:

    Icon
    Description

    Spinner

    The conversion is now being executed.

    Green check mark

    The conversion was successful.

    Red exclamation mark

    The conversion failed on last attempt.

Steps

1

Navigate to the query converter pane

From the Code Generation tab, click the Query Converter pane.

2

Open the SQL query converter view

  • If it is your first time using the query converter in your project, click Paste SQL Query.

  • If you have previously converted SQL code in your project, click the + ADD button on the left-hand pane next to Queries.

3

Enter your SQL query

  1. Copy your SQL query to your clipboard.

    Note

    Queries copied into the SQL Query text field must be a SELECT query. For example: SELECT ID,ITEMNAME,PRICE FROM inventory.

  2. Under the SQL Query header, paste your SQL query into the text box.

4

Select a target language

Use Target Language to select a language for the query converter to render.

Note

If you use Java, you can also select Return a domain object where possible. When selected, the query converter includes strongly typed Java entities based on POJO classes if the query only operates on a single MongoDB collection.

5

Convert and test syntax

  1. Click the Convert button. Wait for your SQL code to converted. The converted MongoDB syntax code displays in the Converted MongoDB Query pane.

    If the query converter has errors, you can view the details in the Converted MongoDB Query pane.

    Tip

    To rename or delete queries from your project, click the button on the SQL Query pane and select the or icon.

  2. Click the icon to copy the MongoDB syntax to your clipboard.

  3. Test and validate the generated MongoDB syntax.

    Tip

    You can use the Filter text box to filter queries, stored procedures, triggers, packages, and views based on object name and SQL syntax.

6

(Optional) Provide feedback on the converted query

Provide feedback on converted queries to help improve future query conversions. To provide feedback, click the or icon on the bottom right corner of the converted query panel. Then, you can provide feedback details and click Submit.

Examples

The following table shows examples of SQL queries converted to MongoDB syntax using query converter. The syntax and structure of the converted queries vary depending on the mapping rules used in the project:

SQL Syntax
MongoDB Syntax
SELECT *
FROM CUSTOMERS
async function query(db) {
  return await db.collection('Customers').find({
  }).toArray();
}
SELECT CONTACTNAME, CITY
FROM CUSTOMERS
WHERE CONTACTNAME LIKE '%SMITH%'
async function query(db) {
return await db.collection('Customers').find({
      ContactName: { $regex: '.*SMITH.*' }
   }, {
      projection: { ContactName: 1, City: 1, _id: 0 }
   }).toArray();
}
SELECT CUSTOMERID, CITY
FROM CUSTOMERS AS C
JOIN ORDERS AS O
   ON C.CUSTOMERID = O.CUSTOMERID
WHERE CONTACTNAME IN('ABI','JIM')
const query = async (db) => {
return await db.collection('Customers').aggregate([
   {
      $lookup: {
         from: 'Orders',
         localField: 'CustomerId',
         foreignField: 'CustomerId',
         as: 'customer_orders'
      }
   },
   {
      $match: {
      CONTACTNAME: { $in: ['ABI', 'JIM'] }
      }
   },
   {
      $project: {
      CustomerId: 1,
      City: 1
      }
   }
   ]).toArray();
};

Next Steps

Learn More

  • SQL to MongoDB Mapping Chart

  • SQL to Aggregation Mapping Chart

Back

Enable Query Converter

Next

Convert Stored Procedures