PowerExchange for MongoDB User Guide

Back Next

Virtual Tables - An Example

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

in the Developer tool.

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_vt_main: The following table shows the schema of CustomerTable_vt_main 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_vt_Invoices: The following table shows the schema of CustomerTable_vt_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_vt_Contacts: The following table shows the schema of CustomerTable_vt_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_vt_Ratings: The following table shows the schema of CustomerTable_vt_Ratings virtual table:

CustomerTable.id
Ratings_index
Ratings_value

1111
1
7

1111
2
8

2222
1
5

2222
2
6

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