Graphlytic 5.1.1

Table of Contents

1. Description

Connects MongoDB using the Mongo Java driver.

2. Connection

2.1. Attributes

Name	Description	Example
id	This ETL connection’s ID, which will be used as a reference with connection-id within scripts and queries.	id=”mongoInstance”
driver	Use “mongodb” in this case.	driver=”mongodb”
url	MongoDB connection string	url=”mongodb://root:12345678@localhost:27017/my_local_database?authSource=admin”
user	MongoDB username. Will throw an error if username and password are already defined in the URL.	user=”root”
password	MongoDB password. Will throw an error if username and password are already defined in the URL.	password=”12345678”

See “" section in ETL: PostgreSQL#5.-Examples for example usage.

2.2. Parameters

No additional connection parameters.

3. Query

Executes a MongoDB query and returns the query result. The name of the result parameters corresponds to the properties of the returned document. Property names are used as return variable names (for example, $_id, $name, …).

To mark substitution parameters, use '?'.

Supported query operation:

{
    operation: 'find',
    collection: 'users',
    data: { firstname: { $in: ['Absolon', 'Adam', 'Alex', 'Alexander'] } }
}

The ‘data’ parameter is used as a db.collection.find filter parameter.

4. Script

Executes either an insert, update, or runCommand statement.

Transactions are currently not supported.

To mark substitution parameters, use '?'. See example below.

Supported script operations:

{
  operation: 'insertOne',
  collection: 'articles',
  data: {
    article_id: '?rownum',
    title: '?title',
    content: '?content',
    comments: []
  }
}

{
  operation: 'update',
  collection: 'articles',
  data: [
    <!-- Filter.  Use value of article_id property from ARTICLES table -->
    {article_id: '?article_id'},
    <!-- Update. Use value of text column from COMMENTS table. see https://www.mongodb.com/docs/manual/reference/operator/update/#std-label-update-operators-->
    {$push: {comments: '?text'}},
    <!-- Optional upsert flag, default false which means if no document match, no new document will be created. -->
    false,
    <!-- Optional multiple flag, default false results in usage of either updateOne() or updateMultiple() -->
    false
  ]
}

{
  operation: 'runCommand',
  data: { drop: 'users' }
}

5. Examples

Query example: Executes a query, and every record from the result is written into a text file. The script element is used in the Query element to process every record of the result.

<!DOCTYPE etl SYSTEM "https://scriptella.org/dtd/etl.dtd">
 <etl>
    <description>Example of mongodb query</description>
                
    <properties>
        job_name=Graphlytic job
    </properties>
            
    <connection id="mongodb" driver="mongodb" url="mongodb://root:12345678@localhost:27017/my_local_database?authSource=admin">
    </connection>
    
    <connection id="logInfo" driver="log">
        level=INFO
    </connection>
                
    <script connection-id="logInfo">
        STARTING JOB "$job_name"
    </script>
        
    <!-- Query all documents in users table -->
    <query connection-id="mongodb">
        {
            operation: 'find',
            collection: 'users',
            data: {}
        }
        <!-- Log found documents -->
        <script connection-id="logInfo">
            found user: [$user_id] $firstname $lastname
            posts: ${posts[0].id}, ${posts[1].id}, ${posts[2].id}
        </script>
    </query>
    
</etl>

Script example: Clear two collections. Query Neo4j and create two entries in MongoDB. One in the ‘users’ collection and one in the ‘family’ collection.

<!DOCTYPE etl SYSTEM "https://scriptella.org/dtd/etl.dtd">
 <etl>
    <description>Example of mongodb script</description>
                
    <properties>
        job_name=Graphlytic job
    </properties>
            
    <!-- CONNECTIONS -->
    <connection id="graphdb" driver="graphConnection">
        project_id=1
    </connection>
            
    <connection id="mongodb" driver="mongodb" url="mongodb://root:12345678@localhost:27017/my_local_database?authSource=admin">
    </connection>
    
    <connection id="logInfo" driver="log">
        level=INFO
    </connection>
                
    <!-- JOB STEPS -->
    <script connection-id="logInfo">
        STARTING JOB "$job_name"
    </script>
    
    <!-- Clear 'users' collection -->
    <script connection-id="mongodb">
    {
        operation: 'runCommand',
        data: { drop: 'users' }
    }
    </script>
    
    <!-- Clear 'family' collection -->
    <script connection-id="mongodb">
    {
        operation: 'runCommand',
        data: { drop: 'family' }
    }
    </script>
    
    <query connection-id="graphdb">
        MATCH (n:Person) RETURN id(n) as id, n.Surname as surname, n.Name as name LIMIT 10
        <script connection-id="mongodb">
        [
        {
            operation: 'insertOne',
            collection: 'users',
            data: {
                user_id: '?id',
                firstname: '?name',
                lastname: '?surname',
                posts: [{"id": "?{rownum * 3}"}, {"id": "?{rownum * 3 + 1}"}, {"id": "?{rownum * 3 + 2}"}]
            }
        },
        {
            operation: 'insertOne',
            collection: 'family',
            data: {
                family_id: '?rownum',
                name: '?surname'
            }
        }
        ]
        </script>
    </query>    
    
    <script connection-id="mongodb">
        [
        {
            operation: 'update',
            collection: 'users',
            data: [
                { lastname: { $in: ['Doe'] },
                  firstname: { $in: ['John', 'Jane'] } },
                {$set: { firstname: 'N/A', lastname: 'N/A'}}, 
                false, 
                true
            ]
        }
        ]
    </script>
    
    
    <query connection-id="mongodb">
        [
        {
            operation: 'find',
            collection: 'users',
            data: {lastname: { $in: ['Knirs', 'Farkas'] } }
        }
        ]
 
        <script connection-id="logInfo">
            found user: [$user_id] $firstname $lastname
            posts: ${posts[0].id}, ${posts[1].id}, ${posts[2].id}
        </script>
        
    </query>
</etl>