Docs Menu
Docs Home
/
Relational Migrator
/
Mapping Rules
/
Rule Options

Embedded Documents

On this page

  • About this Task
  • Steps
  • Examples

Use the Embedded Documents mapping rule to denormalize a foreign key relationship. With Embedded Documents you can nest child foreign key fields in a parent collection.

You have two main options when denormalizing your relational data with the Embedded Documents option:

  • You can embed the child documents into the parent and designate a Prefix and Field name.

  • You can merge into the parent, which represents the rows as fields at the parent level.

The Embedded Documents mapping rule is an advanced mapping rule option. It must be explicitly added to a mapping model.

About this Task

  • The Embedded Documents mapping rule is available if the relational table it is mapping from is on the one side of the foreign key relationship.

  • The table containing the foreign key must also be mapped to a collection in the MongoDB model.

Steps

1

Select a table or collection

  1. In the left Schema model pane, click on a collection under the MongoDB or Relational header.

    This prompts the Mappings pane to open on the right of the screen.

2

Create or edit a rule

  1. From the Mapping screen, click + Add to create a new mapping rule or click the icon to edit an existing rule.

  2. Select Embedded documents under Migrate table as.

3

Configure the root path

  1. Select the name of the Source table or Parent collection from the drop-down.

  2. Configure the root path:

    • Prefix: Specifies the rule hierarchy.

    • Field name: Specifies the field containing the embedded array or document.

4

(Optional) Configure multiple foreign keys

Allows you to select which foreign key to embed on when there is more than one foreign key between two tables. This option only appears for tables or collections with multiple foreign key links.

  1. Select the name of the foreign key to embed on from the Foreign key link drop-down.

5

(Optional) Merge fields into the parent

  1. Merge table rows as fields at the parent collection level by clicking the icon next to the field name.

6

(Optional) Configure mapping rule filters

Allows you to explicitly include rows where an expression returns true. This differs from Table Filters, which filter based on an SQL query, and apply to all rows from a particular table. Mapping rule filters only apply to a specific mapping.

  1. On the Mappings pane, click the icon next to Advanced settings.

  2. Select the Add mapping rule filter icon.

  3. Enter a valid JavaScript filter expression in the Value expression text box.

7

Configure fields

  1. Define the options for the new mapping rule. When defining the mapping rule options, you can:

    • Change the collection name.

      To change the collection name, click the Name text box and enter the new name. Click outside of the text box to save the change. If the name is already in use, you receive a validation error.

    • Add Calculated Fields to create new fields based on relational source columns. See Calculated Fields for more information.

    • Change field names.

    • Include or exclude a field from your sync job by clicking the icon next to the field name.

8

Save and close

  1. Click Save And Close.

Examples

This section demonstrates the relational input and the MongoDB output of the Embedded Documents mapping rule.

Embedded Documents

In this example:

  • A Embedded Documents mapping rule is applied to the Order table.

  • The OrderLine table has two rows and the Order table has one row.

  • The parent collection is set to OrderLine.

  • Merge fields into the parent is not selected.

  • The Field name is set to order.

Click the tabs below to see the input and output for this example:

OrderLine table:

OrderLineID
OrderID
ProductID
Quantity
1
1
1
1
4
1
3
1

Order table:

OrderID
CustomerID
TotalAmount
OrderStatusID
1
1
550.00
1

The documents in the OrderLine collection now has the Order details embedded as an embedded document called Orders.

{
ObjectID: {
   OrderLineID: 1,
},
OrderID: 1,
ProductID: 1,
Quantity: 1,
Orders: {
   OrderID: 1,
   CustomerID: 1,
   TotalAmount: 550,
   OrderStatusID: 1,
},
},
{
ObjectID: {
   OrderLineID: 4,
},
OrderID: 1,
ProductID: 3,
Quantity: 1,
Orders: {
   OrderID: 1,
   CustomerID: 1,
   TotalAmount: 550,
   OrderStatusID: 1,
},
}

Merge fields into the parent

In this example:

  • The OrderLine table has four rows and the Order table has three rows.

  • A Embedded Documents mapping rule is applied to the Order table.

  • The parent collection is set to OrderLine.

  • Merge fields into the parent is selected.

  • The Prefix is set to (root).

Click the tabs below to see the input and output for this example:

OrderLine table:

OrderLineID
OrderID
ProductID
Quantity
1
1
1
1
2
2
2
1
3
3
3
1
4
1
3
1

Order table:

OrderID
CustomerID
TotalAmount
OrderStatusID
1
1
550.00
1
2
2
100.00
2
3
3
500.00
3

The documents in the OrderLine collection have the Order details merged into the parent document as fields.

{
_id: {
   orderLineId: 1,
},
orderId: 1,
productId: 1,
quantity: 1,
customerId: 1,
orderStatusId: 1,
totalAmount: 550,
},
{
_id: {
   orderLineId: 2,
},
orderId: 2,
productId: 2,
quantity: 1,
customerId: 2,
orderStatusId: 2,
totalAmount: 100,
},
{
_id: {
   orderLineId: 3,
},
orderId: 3,
productId: 3,
quantity: 1,
customerId: 3,
orderStatusId: 3,
totalAmount: 500,
},
{
_id: {
   orderLineId: 4,
},
orderId: 1,
productId: 3,
quantity: 1,
customerId: 1,
orderStatusId: 1,
totalAmount: 550,
}

Back

Embedded Array

Next

Mapping Rule Filters