MongoDB
    • 30 Mar 2025
    • 5 Minutes to read
    • Dark
      Light
    • PDF

    MongoDB

    • Dark
      Light
    • PDF

    Article summary

    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>
    );
    
    1. Add Translator Properties. Refer to the Translator Properties section for more information.
    2. Click Create

    Translator Properties

    PropertyDefaultDescription
    authMechanismSCRAM-SHA-256Specifies the authentication mechanism.
    sslfalseEnables or disables SSL/TLS connections.
    connectTimeout30000 (30 secs)Sets the connection timeout (in ms).
    socketTimeout60000 (60 secs)Sets the socket timeout (in ms).
    aggregatetrueAllows the use of aggregation pipelines for more complex data transformations.
    batchSize500Sets the number of documents returned in a single batch.
    excludeTablesnullRegular expression to exclude tables from import.
    includeTablesnullRegular expression to include tables for import.
    sampleSize1Number of documents to sample to determine the structure.
    fullEmbeddedNamesfalsePrefixes embedded table names with their parent names to avoid conflicts with existing tables.
    supportMatchBeforeUnwindfalseImproves document processing efficiency.
    preserveNullAndEmptyArraystrueControls how empty or null arrays are handled.
    queryPlannerdisabledEnables MongoDB's query planner explanation for easier troubleshooting and tuning. @Hod - this is not tested
    $unwind

    For information regarding the $unwindcapability andpreservedNullAndEmptyArrays property, refer to MongoDB's documentation. :::


    Supported Capabilities and Usage Examples

    The MongoDB translator is built on the MongoDB aggregation framework, which requires a MongoDB version that supports this framework. The translator supports SQL-like query conversion, enabling operations such as matching, filtering, sorting, grouping, joining, and limiting results. These capabilities allow structured querying of MongoDB collections while maintaining relational-style operations.Below are the MongoDB translator's key capabilities with examples:

    Additional Teiid Metadata Properties

    The MongoDB translator includes two metadata properties to support embedded documents. These properties help structure data when defining relationships between collections. For more details, see the DDL and JSON examples below the table:

    PropertyDescription
    teiid_mongo:EMBEDDABLEAllows the table data to be stored both in its own collection and as an embedded document in related parent collections. Useful for independent entities like Category, which can also be embedded in Products. Supports nested embedding with some limitations.
    teiid_mongo:MERGEMerges a table's data into its parent collection, maintaining only one copy. The parent is determined by foreign key relationships.

    :::(Warning) (Using Teiid Properties) A table can use either the teiid_mongo:EMBEDDABLE or teiid_mongo:MERGE property to define nesting in MongoDB, but not both.

    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.

    Was this article helpful?