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 ODBC 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 ODBC Connector 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 the sampling process, then 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 ODBC. 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 ODBC layer, the name of each virtual table is formed using the name of the real table that contains the array or object, an underscore (_), 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_dim1_idx 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_dim1_idx type name

1111

0

primary

John Johnson

1111

1

invoicing

Jill Jilliamson

2222

0

primary

Jane Doe
_id Ratings_dim1_idx 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_dim1_idx, Contacts_dim1_idx, and Ratings_dim1_idx 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:

INSERT INTO CustomerTable_Ratings (_id, Ratings_value) VALUES (1111, 7)

The following examples show supported cases for passdown of the LIKE filter and are based on the following document in MongoDB:

_id one_dim_array nested_arrays multi_dim_array

Row1

[ "pears", "oranges" ]

[ { "fruit": "pear", "varieties": [ "Bosc", "Bartlett" ] }, { "fruit": "orange", "varieties": [ "Navel", "Cara Cara" ] } ]

[ [ { "fruit": "pear", "varieties": [ "Bosc", "Bartlett" ] } ], [ { "fruit": "orange", "varieties": [ "Navel", "Cara Cara" ] } ] ]

The LIKE filter is on an _id column:

SELECT * FROM fruits_nested_arrays_varieties WHERE _id LIKE 'r%'

The LIKE filter is on a column that is nested within a one-dimensional array:

SELECT * FROM fruits_one_dim_array WHERE fruits_one_dim_array LIKE 'p%'

SELECT * FROM fruits_nested_arrays_varieties WHERE fruits_nested_arrays_varieties LIKE '%a%'

The LIKE filter is on an _id column or a column that is nested within a one-dimensional array and is combined with an AND condition:

SELECT * FROM fruits_nested_arrays_varieties WHERE fruits_nested_arrays_varieties LIKE '%a%' AND _id = 'Row1'

SELECT * FROM fruits_nested_arrays_varieties WHERE _id LIKE '%Row1' AND fruits_nested_arrays_varieties != 'Bosc'

The LIKE filter is on an _id column or a column that is nested within a one-dimensional array and is combined with an OR condition:

SELECT * FROM fruits_nested_arrays_varieties WHERE fruits_nested_arrays_varieties LIKE '%a%' OR fruits_nested_arrays_varieties LIKE 'b%'

SELECT * FROM fruits_nested_arrays_varieties WHERE fruits_nested_arrays_varieties LIKE '%a%' or _id = 'Row1'

A LIKE query on a column that is nested within a multi-dimentional array cannot be passed down to MongoDB:

SELECT * FROM fruits_multi_dim_array_dim2_varieties WHERE fruits_multi_dim_array_dim2_varieties LIKE '%a%'

The above query will not be passed down to MongoDB, but the connector will handle the query to return the expected result.

A query containing an OR condition between LIKE and non-LIKE filters on virtual table cannot be passed down to MongoDB:

The MongoDB does not support passdown of non-LIKE filters on virtual tables to MongoDB. The query will not be passed down to MongoDB, but the connector will handle the query to return the expected result.

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

Related topics