Virtual Tables

One advantage of the MongoDB design is the ability to store data that is denormalized into a fewer number of tables. However, the JDBC interface does not natively support accessing denormalized data types such as arrays and objects. By expanding the data contained within arrays and objects into virtual tables, the Simba MongoDB JDBC Driver allows you to directly interact with the data but leave the storage of the data in its denormalized form in MongoDB.

If any columns are mapped to a denormalized data type during sampling, the connector creates the following tables and saves them as part of the schema definition:

• A base table, which contains only the normal data from the real table.
• A virtual table for each column of denormalized data, expanding the nested data.

Virtual tables refer to the data in the real table, enabling the connector to access the denormalized data. By querying the virtual tables, you can access the contents of arrays and objects via JDBC. When you write or modify data in a virtual table, the data in the real table in the MongoDB database is updated.

The base table and virtual tables appear as additional tables in the list of tables that exist in the database, and are named using the following conventions:

• The base table uses the same name as the real table that it represents.
• In the JDBC layer, the name of each virtual table is formed using the name of the real table that contains the array or object, an underscore character (_), and the name of the array or object.
• In the MongoDB layer, the name of each virtual table is formed using the name of the collection that the data comes from, a period (.), and then the name of the array or object followed by a set of closed square brackets ([]) for each hierarchy level in which the array or object is nested.
• If a virtual table or column has the same name as an actual table or column in the database, then the connector appends _1 to the virtual table or column name as a suffix. The number increments as necessary until the name is unique.

For example, consider the example MongoDB table named CustomerTable shown below.

_id	Customer Name	Invoices	Service Level	Contacts	Ratings
1111	ABC	[{invoice_id: "123", item: "toaster", price: "456", discount: "0.2"}, {invoice_id: "124", item: "oven", price: "1235", discount: "0.2"}]	Silver	[{type: primary, name: "John Johnson"}, {type: invoicing, name: "Jill Jilliamson"}]	[5,6]
2222	XYZ	[{invoice_id: "135", item: "fridge", price: "12543", discount: "0.0"}]	Gold	[{type: primary, name: "Jane Doe"}]	[1,2]

CustomerTable has two columns that have an array of objects in each cell, Invoices and Contacts, and one column that has an array of Scalar types, Ratings. Multiple virtual tables would be generated for this single source table. The first table is the base table, which is shown below.

_id	Customer Name	Service Level
1111	ABC	Silver
2222	XYZ	Gold

The base table contains all of the data of the original table, but the data from the arrays has been omitted and will be expanded in the virtual tables.

The following three tables show the virtual tables that represent the original arrays in the example.

_id	Invoices_index	invoice_id	item	price	discount
1111	0	123	toaster	456	0.2
1111	1	124	oven	1235	0.2
2222	0	135	fridge	12543	0.0

_id	Contacts_index	type	name
1111	0	primary	John Johnson
1111	1	invoicing	Jill Jilliamson
2222	0	primary	Jane Doe

_id	Ratings_index	Ratings_value
1111	0	5
1111	1	6
2222	0	1
2222	1	2

Each of these tables contain the following:

• A reference back to the original primary key column corresponding to the row of the original array (via the _id column).
• An indication of the position of the data within the original array (using the Invoices_index, Contacts_index, and Ratings_index columns).
• The expanded data for each element within the array:
  • Invoices: invoice_id, item, price and discount.
  • Contacts: type and name.
  • Ratings: Ratings_value.

You can select, insert, and update data in the base tables and virtual tables as if they were standard relational tables, and the connector will handle the storage details within MongoDB.

For example, to append 7 to the Ratings array in the CustomerTable where _id = 1111, execute the following statement:

Some operations might be processed differently or not supported for certain types of data. For more information, see Write-Back.

Related topics

• Data Types
• Write-Back
• Features
• Installing and Using the Simba MongoDB JDBC Driver
• Configuring the Simba MongoDB JDBC Driver on page 1
• Driver Configuration Options on page 1