Convert MQL to C# Driver Atlas Search Syntax

MongoDB Atlas Atlas Search
, , ,
1

I am trying to convert the following MQL query to the equivalent c# driver syntax but am facing some issues. When executing the MQL directly in Compass Aggregates, the query returns results, however when using the c# driver 0 results are returned.

{
    $search: {
      compound: {
        should: [
          {
            embeddedDocument: {
              operator: {
                compound: {
                  should: [
                    {
                      embeddedDocument: {
                        operator: {
                          compound: {
                            should: [
                              {
                                text: {
                                  query: "32590",
                                  path: "Roles.Attributes.Value",
                                },
                              },
                            ],
                          },
                        },
                        path: "Roles.Attributes",
                      },
                    },
                  ],
                },
              },
              path: "Roles",
            },
          },
          {
            autocomplete: {
              query: "joe",
              path: "FirstName",
            },
          },
          {
            autocomplete: {
              query: "joe",
              path: "LastName",
            },
          },
          {
            autocomplete: {
              query: "joe",
              path: "Username",
            },
          }
        ],
      },
      index: "role_searching_idx",
    },
  },
  {
    $match: {},
  },
  {
    $facet: {
      count: [
        {
          $count: "count",
        },
      ],
      users: [
        {
          $skip: NumberLong(0),
        },
        {
          $limit: NumberLong(50),
        },
      ],
    },
  },
]}

c# Attempt:

var countFacet = AggregateFacet.Create("count", PipelineDefinition<User, AggregateCountResult>.Create(new[]
        {
            PipelineStageDefinitionBuilder.Count<User>()
        }));

var usersFacet = AggregateFacet.Create("users", PipelineDefinition<User, User>.Create(new[]
            {
                PipelineStageDefinitionBuilder.Skip<User>((page - 1) * size),
                PipelineStageDefinitionBuilder.Limit<User>(size)
            }));

var filter = Builders<User>.Filter.Eq(f => f.Status, status);

var aggregates = collection.Aggregate()
  .Search(Builders<User>.Search
  .Compound()
  .Should(
    Builders<User>.Search.EmbeddedDocument(u => u.Roles, rolesQuery),
    Builders<User>.Search.Autocomplete(a => a.FirstName, query),
    Builders<User>.Search.Autocomplete(a => a.LastName, query),
    Builders<User>.Search.Autocomplete(a => a.Username, query),
    Builders<User>.Search.Autocomplete(a => a.Profile.Reference, query),
 ), indexName: "role_searching_idx")
 .Match(filter)
 .Facet(countFacet, usersFacet)
 .ToListAsync();

var count = results.First()
 .Facets.First(x => x.Name == "count")
 .Output<AggregateCountResult>()?
 .FirstOrDefault()?
 .Count ?? 0;

var users = results.First()
 .Facets.First(f => f.Name == "users")
 .Output<User>()?
 .ToList();

Any ideas where I am going wrong?

2

Hi, @Grant_Perkins1,

Welcome to the MongoDB Community Forums. I understand that you are trying to translate MQL into the equivalent aggregation pipeline in the .NET/C# Driver.

If you already have the MQL, you could build it up using BsonDocument. For example:

var stages = BsonDocument.Parse(mqlStages);
var pipeline = EmptyPipelineDefinition<BsonDocument>.Create(stages);
var query = coll.Aggregate<BsonDocument>(pipeline);

You may want to use POCOs (e.g. User) rather than BsonDocument.

If you want to express the pipeline in C#, I would recommend rendering the query to the console to compare the MQL generated by the driver versus your desired MQL.

var query = coll.Aggregate().Match(x => x.FirstName == "James");
Console.WriteLine(query);

This way you can see where your MQL differs and may give you an idea of how to update your C# code to achieve the desired MQL.

Hope this helps!

Sincerely,
James