Convert Views

On this page

About this Task

Before you Begin
Steps
Examples
Next Steps
Learn More

The query converter takes the SQL to create the view and converts it to the equivalent MQL. The query converter considers the mapping rules and schema transformations defined in your project when converting your SQL code. For details on the conversion process, see convert view examples.

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.

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.

Before you Begin

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

Steps

Navigate to the query converter pane

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

Open the query converter view

If it is your first time using the query converter in your project, click Import From Database.
If your project already has converted SQL code, click the Manage Database Objects button on the left-hand pane.

Connect to your relational database

Specify the connection details to your source database.
For details, see Relational Database Connection Strings.
Click Connect.

Select views

On the Import Database Objects modal, click your database.
Click the icon next to your schema.
Click the icon next to Views.
To select the views to convert, click the icon next to a view's name. By default, all views are selected.
Click Save.

Convert and test code

Click a view's name from the left-hand pane under Views.
The SQL code is displayed in the Imported View pane.
Click the Convert button. Wait for the query converter to convert your code.
The converted MongoDB code is displayed in the Converted MongoDB Query pane.
If the query converter has errors, you can view the details in the Converted MongoDB Query pane.
Click the icon to copy the MongoDB code to your clipboard.
Test and validate the generated MongoDB code.

Tip

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

Examples

The examples below show PostgreSQL views from the example Northwind database converted to MongoDB code.

If you want to create a MongoDB view from the query:

Copy the aggregation pipeline syntax from the aggregate command.
Use the aggregation pipeline syntax with the createView shell command.

The following example shows an inner join view converted to MongoDB:

SQL Syntax

MongoDB Syntax

SELECT
O.ORDER_ID,
O.CUSTOMER_ID,
O.ORDER_DATE,
OD.UNIT_PRICE,
OD.DISCOUNT
FROM ORDERS AS O
INNER JOIN ORDER_DETAILS AS OD
   ON O.ORDER_ID = OD.ORDER_ID;

async function query(db) {
  return await db.collection('orders').aggregate(
  [
   {
      $lookup:
      {
         from: "orderDetails",
         localField: "orderId",
         foreignField: "orderId",
         as: "orderDetails"
      },
   },
   {
      $unwind: "$orderDetails",
   },
   {
      $project:
         {
            orderId: 1,
            customerId: 1,
            orderDate: 1,
            unitPrice: "$orderDetails.unitPrice",
            discount: "$orderDetails.discount"
         }
   }
  ]
  ).toArray();
 }

The shell command to create this view in MongoDB is:

db.createView("VW_OrderDetails", "orders",
   [
    {
      $lookup:
       {
         from: "orderDetails",
         localField: "orderId",
         foreignField: "orderId",
         as: "orderDetails"
       },
    },
    {
       $unwind: "$orderDetails",
    },
    {
       $project:
          {
            orderId: 1,
            customerId: 1,
            orderDate: 1,
            unitPrice: "$orderDetails.unitPrice",
            discount: "$orderDetails.discount"
          }
    }
   ]
 )

The following example shows an aggregated group by view converted to MongoDB:

SQL Syntax

MongoDB Syntax

SELECT
POSTAL_CODE,
COUNT(*) AS CUSTOMERCOUNT
FROM CUSTOMERS
GROUP BY POSTAL_CODE
ORDER BY CUSTOMERCOUNT DESC;

async function query(db) {
   return db.collection('customers').aggregate(
   [
      {
         $group:
         {
           _id: "$postal_code",
           customercount: { $sum: 1 }
         }
      },
      {
         $sort: { customercount: -1 }
      }
   ]
  ).toArray();
}

The shell command to create this view in MongoDB is:

db.createView("VW_Customers", "customers",
   [
      {
         $group:
         {
            _id: "$postal_code",
            customercount: { $sum: 1 }
         }
      },
      {
         $sort: { customercount: -1 }
      }
   ]
)

Next Steps

Learn More

Back

Convert Triggers

Bulk Conversion