PowerExchange for MongoDB JDBC User Guide for PowerCenter

10.4.1
- 10.5.7
- 10.5.6
- 10.5.4
- 10.5

Back Next

Virtual Tables Example

The collection CustomerTable contains arrays. You want to create virtual tables from the arrays and import the virtual tables as data objects in Informatica Cloud.

The following table shows the schema of CustomerTable collection:

id	Customer Name	Invoices	Service Level	Contacts	Ratings
1111	John	[{invoice_id=123,item=toaster, price=456,discount=0.2}, {invoice_id=124,item=oven, price=12345, discount=0.3}]	Silver	[{type=primary,name="John Johnson"}, {type=invoicing,name="Jane Johnson"}]	[7,8]
2222	Jane	[{invoice_id=125,item=blender, price=7456,discount=0.5},	Gold	[{type=primary,name="Jane Johnson"}	[5,6]

If you enable virtual table detection, the driver creates the following virtual tables:

CustomerTable: The main table uses the same name as the original table that it represents.; The following table shows the schema of CustomerTable virtual table:

id
Customer Name
Number of Invoices
Service Level
Number of Contacts
Number of Ratings

1111
John
2
Silver
2
2

2222
Jane
1
Gold
1
2
CustomerTable_Invoices: The following table shows the schema of CustomerTable_Invoices virtual table:

CustomerTable.id
Invoices_index
invoice_id
item
price
discount

1111
1
123
toaster
456
0.2

1111
2
124
oven
12345
0.3

2222
1
125
blender
7456
0.5
CustomerTable_Contacts: The following table shows the schema of CustomerTable_Contacts virtual table:

CustomerTable.id
Contacts_index
type
name

1111
1
primary
John Johnson

1111
2
invoicing
Jane Johnson

2222
1
primary
Jane Johnson
CustomerTable_Ratings: The following table shows the schema of CustomerTable_Ratings virtual table:

CustomerTable.id
Ratings_index
Ratings_value

1111
1
7

1111
2
8

2222
1
5

2222
2
6

You cannot use the Designer to preview virtual tables. You cannot use a DD_DELETE strategy in an Update Strategy transformation to delete rows from a virtual table.

CustomerTable.id	Invoices_index	invoice_id	item	price	discount
1111	1	123	toaster	456	0.2
1111	2	124	oven	12345	0.3
2222	1	125	blender	7456	0.5

CustomerTable.id	Contacts_index	type	name
1111	1	primary	John Johnson
1111	2	invoicing	Jane Johnson
2222	1	primary	Jane Johnson

Rename Saved Search

Table of Contents

PowerExchange for MongoDB JDBC User Guide for PowerCenter

PowerExchange for MongoDB JDBC User Guide for PowerCenter

Virtual Tables Example

Virtual Tables Example