MongoDB External Bindings

In GlobalSearch 6.1.103+ the “External Binding” Feature was added to lists in GlobalSearch, there are currently two options for this, SQL and MongoDB, this article covers how to use the MongoDB binding to populate a GlobalSearch list.

You can learn how to check your GlobalSearch version by reading our https://knowledge.square-9.com/s9skb/Determine-Software-Version.979894356.html knowledge base page

1. Navigate to the Field Catalog and then the Lists Subsection

2. Create a new list and turn on the External Binding option

3. Select MongoDB Query from the drop down

Connection Strings

MongoDB connection strings are

mongodb://hostname/dbName
mongodb://localhost/QuickbooksSync

You can also include authentication credentials if you are using MongoDB with authentication;

mongodb://username:password@hostname/dbName

You can view the MongoDB documentation on the connection string format at the link below;

https://www.mongodb.com/docs/manual/reference/connection-string/

MongoDB Query

The JSON document input here is executed via db.runCommand(), it is not within the Scope of this article to detail how MongoDB Queries work but there will be numerous examples that should get you pointed in the correct direction.

For the examples here we will be pulling information from a QuickbooksSync MongoDB Database. You can find a complete list of the S9QBItemTypes at the https://knowledge.square-9.com/s9skb/QuickSync-and-QuickBill-Guide.988217303.html .

Aggregation Queries

https://www.mongodb.com/docs/manual/core/aggregation-pipeline/

An Aggregation Pipeline is a way of processing MongoDB data through a number of different stages, we will be using some of the more common stages, $match, $project and $sort.

You may get “JSON reader was expecting a name but found { if you are trying to project values that do not exist, be sure to include a filter in the $match stage that checks that the value exists.

Get Companies from Quickbooks

{
   aggregate: 'Company Name',
   pipeline: [
      
      { $match: {'Value.S9QBItemType': 'Account',  'Value.FullName':{$exists:true}}},
      { $project: {_id: 0, 'ListValue': '$Value.FullName'} },
      { $sort: { 'ListValue': 1 } },
   ],
   cursor: { batchSize: 500 }
}

$match

https://www.mongodb.com/docs/v4.4/reference/operator/aggregation/match/

You can add as many additional filter criteria as you want to the $match stage;

To filter and show only companies that are active;

{$match: {'Value.S9QBItemType': 'Account', 'Value.Active': true}}

$project

https://www.mongodb.com/docs/v4.4/reference/operator/aggregation/project/#mongodb-pipeline-pipe.-project

Note that you need to both set the ListValue and _id set to 0 in order for GlobalSearch to interpret the results properly

The projection stage in the pipeline pulls only the information specified from the results of the match stage, this allows us to return the information in the proper format to be used by GlobalSearch. The GlobalSearch list is populated with the value of ListValue in the returned results, if there is not a ListValue in the returned results you will not get anything in your list.

In order to pull information from the documents in our results from the match stage we can use $ syntax; this syntax denotes that we are referencing the document returned by match, if we do not use it the value will be interpreted as a string and will not behave as expected.

We also have to hide the _id value, this is done by setting it to 0 which is interpreted as the boolean false.

$sort

https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort/

We can configure the aggregation pipeline to sort the returned documents based on the time last modified, in order to do so we will have to update both the sort and the project sections of our query;

{$project: {_id:0, ListValue:'$Value.FullName', 'time':'$Value.EditSequence'}},
{$sort:{time:1}}

Here EditSequence is a UNIX timestamp created in QuickSync documents that represents the time the document was last modified by QuickSync.

cursor

The cursor sub-document is used to control the number of items returned by a MongoDB query.

cursor: { batchSize:500 }

Will result in the query returning up to 500 items. Note that the value must be positive.

Find Queries

Find queries only work on MongoDBs 4.4 and newer. 99% of current GlobalSearch installations will be using MongoDB 3.4.6. You can check by running db.version() from a mongo shell.

Get Companies from Quickbooks

{
  "find": "Company Name",
  "filter": {"Value.S9QBItemType":"Account"},
  "projection": {"ListValue":"$Value.Name"}
}

Get only Active Companies from Quickbooks, sorted by Company Name, limiting number of items returned

{
  "find": "Company Name",
  "filter": {"Value.S9QBItemType": "Account", "Value.IsActive":true},
  "projection": {"ListValue": "$Value.Name"},
  "sort": {"Value.Name": 1},
  "batchSize": 5
}

Line by line:

"find": "Company Name", The name of the MongoDB Collection to run your Query against.

"filter": {"Value.S9QBItemType": "Account", "Value.IsActive": "true"}

Note that the boolean (true/false value) is a string here, this is because it is stored in the backend MongoDB as a string, if you are trying to filter against a value that is stored as a boolean you would not need the quotes.

"projection": {"ListValue": "$Value.Name"},

“sort": {"Value.Name": 1}

You can find a full list of the available options here; https://www.mongodb.com/docs/manual/reference/command/find/#mongodb-dbcommand-dbcmd.find