Embedded Array

On this page

About this Task

Steps
Example

Use the Embedded array mapping rule to insert the values of the child table as array elements under documents in the collection mapped to the parent table. The Embedded array mapping rule is an advanced mapping rule option. It must be explicitly added to a mapping model.

About this Task

The Embedded array mapping rule is available if the relational table it is mapping from is on the many side of the foreign key relationship.
The table containing the primary key must also be mapped to a collection in the MongoDB model.

Steps

Select a table or collection

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.

Create or edit a rule

From the Mapping screen, click + Add to create a new mapping rule or click the icon to edit an existing rule.
Select Embedded array under Migrate table as.

Configure the root path

Select the name of the Source table or Parent collection from the drop-down.
Configure the root path:
- Prefix: Specifies the rule hierarchy.
- Field name: Specifies the field containing the embedded array or document.

(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.

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

(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.

On the Mappings pane, click the icon next to Advanced settings.
Select the Add mapping rule filter icon.
Enter a valid JavaScript filter expression in the Value expression text box.

(Optional) Configure array conditions

Allows you to sort an embedded array and limit the amount of entries in that array. You can apply only a sort, only a limit, or both. When limiting to a single entry, there is the option to embed as a document instead of a single-element array by selecting the Create an array of primitive values .

On the Mappings pane, click the icon next to Advanced settings.
Select the Add array conditions icon.
Enter a filter in the Value expression text box.
In the Sort by and order heading, select the source field to sort on and toggle between for ascending and for descending order.
Select a Limit option:
- No limit: No limit
- Limit number of rows: Enter the maximum number of elements returned in the array. Default vaulue is 10.

Note

Excluded fields cannot be sorted on. If a previously selected sorting field is excluded at a later point in time, the array condition is removed.
If an array is modified during the CDC stage of a continuous job, sorting and limiting considers only the newly modified row and the pre-existing array elements, not the other values from the table which are not in the array.

Configure fields

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 migration job by clicking the icon next to the field name.

Save and close

Click Save And Close.

Example

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

Embedded array

In this example:

An Embedded Array mapping rule is applied to the Customer table.
The Source table is set to Order.
The Prefix is set to (root).
The Field Name is set to orders.

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

Customer table:

Id	Name	Address1	Address2	Address3
1	Joelynn Fawthrop	86 Dwight Pass	Carregal	3800-854

Orders table:

OrderID	CustomerID	TotalAmount	OrderStatusID
1	1	50.00	1
4	1	500.00	3

The document in the Customer collection now has the Order details embedded as an array of objects field called Orders.

All of the customers orders are now visible in the Customer collection as an array of nested documents.

{
"_id": {    "CustomerID": 1  },
"Name": "Joelynn Fawthrop",
"Address1": "86 Dwight Pass",
"Address2": "Carregal",
"Address3": "3800-854",
"Orders":
   [
      {  "OrderID": 1,
         "CustomerID": 1,
         "TotalAmount": 50,
         "OrderStatusID": 1
      },
      {  "OrderID": 4,
         "CustomerID": 1,
         "TotalAmount": 500,
         "OrderStatusID": 1
      }
   ]
}

Back

New Documents

Embedded Documents