« MBS Xojo Plugins in v… | Home | Android progress @ Xo… »

Distinct in MongoDB to find all tags from documents

Let's say you added some records into a collection for MongoDB. For example animals and they got tagged with various tags. Now you need the list of all tags to fill a popup menu. You like to avoid looping over all records to collect it yourself. The database server should do that without transferring the whole table to the client!

First let us create a few test records with our MongoCollectionMBS class:

Sub InsertDog() Dim NewRecord As New JSONItem Dim Tag As New JSONItem tag.Value("Name") = "Pet" Dim Tags As New JSONItem tags.Append tag NewRecord.Value("name") = "Dog" NewRecord.Value("tags") = tags Dim Result As String = Collection.InsertOne(NewRecord.toString) details.Text = result End Sub

As you see, the record has the name "Dog" and the tags array contains one entry for pet. Now let's add more records and query them:

{
    "_id":  {
        "$oid": "632753c8c436b7143109f831"
    },
    "name": "Dog",
    "tags": [
        {
            "Name": "Pet"
        }
    ]
}

{
    "_id":  {
        "$oid": "632753c8c436b7143109f832"
    },
    "name": "Cavy",
    "tags": [
        {
            "Name": "Pet"
        }, 
        {
            "Name": "Small"
        }
    ]
}

{
    "_id":  {
        "$oid": "632753c8c436b7143109f833"
    },
    "name": "Cow",
    "tags": [
        {
            "Name": "Livestock"
        }, 
        {
            "Name": "Big"
        }
    ]
}

Now let us run the distinct command to find all values for tags in various documents for that collection. We have the Command function on the MongoCollectionMBS class (or MongoDB.DatabaseCommand for FileMaker), which takes a JSON for the command. And the command is distinct and takes the name of the collection to search. You can pass the name of the field with the key parameter and optionally a query to limit the search to some records, e.g. {"deleted": {$ne : 1}} to find only records where deleted field is not equal to 1.

Sub Action() Dim command As New JSONItem Dim query As New JSONItem // find all tags in the records for animals command.Value("distinct") = "animals" command.Value("key") = "tags" command.Value("query") = query Dim r As String = Database.Command(command.toString) details.Text = r End EventHandler

The result is shown in the text field and it shows the tags like this:

{
  "values": [
    {
      "Name": "Big"
    },
    {
      "Name": "Livestock"
    },
    {
      "Name": "Pet"
    },
    {
      "Name": "Small"
    }
  ],
  "ok": 1
}

Now you can parse that with a JSON class and fill your menu item. Please don't hesitate to contact us if you have questions.

18 09 22 - 19:54