MongoDB

MongoDB Overview

The MongoDB translator provides translation of SQL queries into MongoDB queries and a relational view of MongoDB data. This allows real-time access to complex, document-based data structures. MongoDB's scalability and flexible data models make it ideal for dynamic Policy evaluations.

Setting Up a MongoDB Connection and Data Model

Prerequisites

• MongoDB instance (cloud or self-hosted)
• Connection details (host, port, database name, credentials)

Creating a MongoDB Data Source in the Platform

1. In the Platform, open the Policy Information Point in your Environment Settings.

2. Select a Policy Authorization Group from the dropdown.

3. Click New Data Source.

4. Enter a unique name in the Name field.

5. Under Connection Adapter, choose MongoDB (new).

6. Input the fields in the Connection Settings section:

   • Auth Database - MongoDB Database Name for user authentication in case the Security Type 'MONGODB-CR' is used. This is an optional value.
   • Security Type:
     • None
     • SCRAM_SHA_1 (default)
     • SCRAM_SHA_256
     • MONGODB_CR
     • Kerberos
     • X509
   • Enable SSL if using SSL connections.
   • Remote Server List:
     • Optional Port: host:port[;host:port...]
     • Full standard: (mongodb://) or seedlist: (mongodb+srv://) connection URI string
   • Database: MongoDB database name.
   • Username/Password: Enter your MongoDB credentials either by:
     • Inputting them directly as clear text (not recommended outside of sandbox environments)
     • Referencing the credentials using Environment Variables by enclosing them in brackets to make them more dynamic and secure.
       • E.g. [DOE-USER] [DOE-PASSWORD]

7. In the Models section:

   • Enter a unique name in the Name field.
   • Define the schema using a DDL. Example:

CREATE SCHEMA virtual_schema_name;

CREATE TABLE virtual_schema_name.table_name (
  _id STRING,
  field1 STRING,
  field2 INT,
  embedded_field STRUCT<field3 STRING, field4 DOUBLE>
);

8. Add Translator Properties. Refer to the Translator Properties section for more information.
9. Click Create

Translator Properties

Merge Example

When you want to merge related data directly into a parent collection, use the teiid_mongo:MERGE property. This embeds the child document inside the parent, storing only a single collection in MongoDB.

Example DDL

CREATE FOREIGN TABLE User (
    UserId integer PRIMARY KEY,
    FirstName varchar(25),
    LastName varchar(25)
) OPTIONS(UPDATABLE 'TRUE');

CREATE FOREIGN TABLE Address (
    UserId integer PRIMARY KEY,
    Street varchar(50),
    City varchar(25),
    State varchar(25),
    Zipcode varchar(6),
    FOREIGN KEY (UserId) REFERENCES User (UserId)
) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:MERGE" 'User');

This results in the following collection:

User Collection

{
  "_id": 1,
  "FirstName": "Alice",
  "LastName": "Smith",
  "Address": {
    "Street": "456 Oak St",
    "City": "San Francisco",
    "State": "CA",
    "Zipcode": "94107"
  }
}

In this structure:

• The User collection contains the Address as a subdocument.
• The address data is merged into the parent document, eliminating the need for a separate Address collection.

Embedded Example

If a table is referenced by multiple other tables, use the teiid_mongo:EMBEDDABLE property. This allows MongoDB to store the data both in its own collection and as a subdocument in related collections.

Example DDL

CREATE FOREIGN TABLE User (
    UserId integer PRIMARY KEY,
    FirstName varchar(25),
    LastName varchar(25)
) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');

CREATE FOREIGN TABLE Purchase (
    PurchaseId integer PRIMARY KEY,
    UserId integer,
    PurchaseDate date,
    TotalAmount decimal,
    FOREIGN KEY (UserId) REFERENCES User (UserId)
) OPTIONS(UPDATABLE 'TRUE');

CREATE FOREIGN TABLE Review (
    ReviewId integer PRIMARY KEY,
    UserId integer,
    Content varchar(255),
    FOREIGN KEY (UserId) REFERENCES User (UserId)
) OPTIONS(UPDATABLE 'TRUE');

This results in the following collections:

User Collection

{
  "_id": 1,
  "FirstName": "Alice",
  "LastName": "Smith"
}

Purchase Collection

{
  "_id": 200,
  "UserId": 1,
  "PurchaseDate": ISODate("2024-01-01T00:00:00Z"),
  "TotalAmount": 150.00,
  "User": {
    "FirstName": "Alice",
    "LastName": "Smith"
  }
}

Review Collection

{
  "_id": 50,
  "UserId": 1,
  "Content": "This product is fantastic!",
  "User": {
    "FirstName": "Alice",
    "LastName": "Smith"
  }
}

This structure creates three separate collections, with the User data embedded within both the Purchase and Review collections based on foreign key relationships.

Limitations

• Nested Embedding: MongoDB has limited support for handling deeply nested arrays. Using the EMBEDDABLE property for multiple levels of nesting is supported, but nesting beyond two levels with MERGE is not recommended.
• Document Size: MongoDB enforces a 16 MB document size limit. Deep nesting can quickly increase document size, so use caution to avoid exceeding this limit.
• Joins: When querying related tables, you must use either the EMBEDDABLE or MERGE properties. Without these properties, join queries will fail. If tables are not embedded, set allow-joins=false on the foreign key to prevent query errors.
• Query Depth: Limited to MongoDB’s supported query syntax.
• Data Types: BSON types may not map perfectly to SQL types.
• Transaction Support: Multi-document transactions are limited to replica sets or sharded clusters.