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, or stored procedures. Some queries may not be converted correctly while others may not be converted at all.
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, 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

Navigate to the query converter pane

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

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.

Enter your SQL query

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.
Under the SQL Query header, paste your SQL query into the text box.

Convert and test syntax

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.
Click the icon to copy the MongoDB syntax to your clipboard.
Test and validate the generated MongoDB syntax.
Tip
You can use the Filter text box to filter queries, stored procedures, triggers, and views based on object name and SQL syntax.

(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

Back

Enable Query Converter

Convert Stored Procedures