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
Navigate to the Field Catalog and then the Lists Subsection
Create a new list and turn on the External Binding option
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 S9QBItemType
s 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
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/
Sort Value | Direction |
---|---|
1 | Ascending |
-1 | Descending |
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