2dsphere index throws error

I have a system that needs to perform many different kinds of searches, including pagination, so we’re using aggregation queries to get counts and limit results. The same processes are used for single queries and multi-layer queries.

EG:
I have simple-query A which may or may not have a geolocation limitation
I have simple-query B which may or may not have a geolocation limitation
I have a multi-layer query that can contain query A, query B, or both. It also has it’s own geolocation limitation, and a couple of other default criteria.
We use a 2dsphere index for the geolocation limitation because it dramatically improves performance, however, when my multi-layer query contains two geolocation limits (one within the other) and no other non-geolocation criteria, it throws an error: “Intersection requires two index intervals”
The same query works perfectly without the index.

aggregation query:

[
  {
    $match:
      /**
       * query: The query in MQL.
       */
      {
        $and: [
          {
            /* GLOBAL criteria */
            $and: [
              {
                location: {
                  $geoWithin: {
                    $geometry: {
                      type: "Polygon",
                      coordinates: [
                        [
                          [-85, 27],
                          [-85, 30],
                          [-89, 30],
                          [-89, 27],
                          [-85, 27],
                        ],
                      ],
                      crs: {
                        type: "name",
                        properties: {
                          name: "urn:x-mongodb:crs:strictwinding:EPSG:4326",
                        },
                      },
                    },
                  },
                },
              },
              {
                deleted: false,
              },
              {
                deleted: {
                  $ne: null,
                },
              },
            ],
          },
          {
            $or: [
              {
                /* layer 1 */
                $and: [
                  {
                    location: {
                      $geoWithin: {
                        $geometry: {
                          type: "Polygon",
                          coordinates: [
                            [
                              [-86, 27],
                              [-86, 28],
                              [-87, 28],
                              [-87, 27],
                              [-86, 27],
                            ],
                          ],
                          crs: {
                            type: "name",
                            properties: {
                              name: "urn:x-mongodb:crs:strictwinding:EPSG:4326",
                            },
                          },
                        },
                      },
                    },
                  },
                  {
                    carType: "TAXI",
                  },
                  {
                    onACall: true,
                  },
                  {
                    onACall: {
                      $ne: null,
                    },
                  },
                ],
              },
            ],
          },
        ],
      },
  },
]

Hey @Serena_Cassell,

Welcome to the MongoDB Community!

May I ask what you mean by a multi-layer query? Could you please share an example query?

However to better understand the requirements, could you please share some additional information that will help the community assist you better?

  • Couple of sample documents from your collection,
  • MongoDB version used,
  • Expected result documents,
  • Output of db.collection.getIndexes().

Regards
Kushagra

@Kushagra_Kesav thank you!
I wish I could attach a ZIP file, as I have all the information including document examples, query examples, and steps to reproduce the issue.

The version of MongoDB is 7.0.1

This is the results of getIndexes():

[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2,
    key: { location: '2dsphere' },
    name: 'location_2dsphere',
    '2dsphereIndexVersion': 3
  }
]

This is the document data in CSV format for the example I’ve created:

vehicleId,_class,location.type,location.coordinates[0],location.coordinates[1],carType,onACall,deleted,vehicleName
429c19eb-9e45-4755-bdc0-7dfa0149f48d,ca.app.vehicle.VehicleWrapper,Point,-86.75,27.75,TAXI,TRUE,FALSE,TAXI1
c716ccff-14f8-4443-89e1-9e318310796c,ca.app.vehicle.VehicleWrapper,Point,-87.78,28.23,STARSHIP,,TRUE,STARSHIP1
0f7d95b1-b92b-4ec3-a897-ec0b0fc9aded,ca.app.vehicle.VehicleWrapper,Point,-87.75,28.25,STARSHIP,,FALSE,STARSHIP2
47c9b950-9c38-4261-bfd7-8e92db389ecd,ca.app.vehicle.VehicleWrapper,Point,-87.25,28.25,PERSONAL,,FALSE,PERSONAL123
d256ad4e-7f02-4320-b6f9-ca4461b80e3e,ca.app.vehicle.VehicleWrapper,Point,-85.25,27.25,TAXI,TRUE,FALSE,TAXI2319
36400547-b79c-4a99-823c-289139e092bd,ca.app.vehicle.VehicleWrapper,Point,-88.5,27.5,STARSHIP,,FALSE,STARSHIP3
3542f1c6-2b10-42e9-a873-71200bfc0e26,ca.app.vehicle.VehicleWrapper,Point,-86.25,29.25,PERSONAL,,FALSE,PERSONAL24
2ef81027-7b9e-47d6-b264-de8e67b7fcf1,ca.app.vehicle.VehicleWrapper,Point,-86.25,27.25,TAXI,FALSE,FALSE,TAXI5
79247aac-54fc-4d24-8a17-1494d2c2a535,ca.app.vehicle.VehicleWrapper,Point,-89.25,28.5,STARSHIP,,FALSE,STARSHIP7

The aggregation query in the post works without the 2dsphere index and fails with the 2dsphere index.

An example of another query (that DOES work) that would be generated by the same system/process that creates the broken query is this:

[
  {
    $match:
      {
        $and: [
          {
            /* GLOBAL criteria */
            $and: [
              {
                location: {
                  $geoWithin: {
                    $geometry: {
                      type: "Polygon",
                      coordinates: [
                        [
                          [-85, 27],
                          [-85, 30],
                          [-89, 30],
                          [-89, 27],
                          [-85, 27],
                        ],
                      ],
                      crs: {
                        type: "name",
                        properties: {
                          name: "urn:x-mongodb:crs:strictwinding:EPSG:4326",
                        },
                      },
                    },
                  },
                },
              },
              {
                deleted: false,
              },
              {
                deleted: {
                  $ne: null,
                },
              },
            ],
          },
          {
            $or: [
              {
                /* layer 1 */
                $and: [
                  {
                    location: {
                      $geoWithin: {
                        $geometry: {
                          type: "Polygon",
                          coordinates: [
                            [
                              [-86, 27],
                              [-86, 28],
                              [-87, 28],
                              [-87, 27],
                              [-86, 27],
                            ],
                          ],
                          crs: {
                            type: "name",
                            properties: {
                              name: "urn:x-mongodb:crs:strictwinding:EPSG:4326",
                            },
                          },
                        },
                      },
                    },
                  },
                  {
                    carType: "TAXI",
                  },
                  {
                    onACall: true,
                  },
                  {
                    onACall: {
                      $ne: null,
                    },
                  },
                ],
              },
              {
                /* layer 2 */
                $and: [
                  {
                    carType: "STARSHIP",
                  },
                ],
              },
            ],
          },
        ],
      },
  },
]

A multi-layer query in this case is a “query of queries”. Multiple queries are assembled based on pre-defined criteria from the users, and assembled into a larger query, which is executed and returns a specific page of data.

Basically, in the $match aggregation, we have this structure:

$and: [
    { /* global criteria, including a geospatial area */ },
    { $or: [
        { /* sub query 1 */ },
        /* ... repeat for as many queries as there are to combine ... */
        { /* sub query x */ },
    ]}
]

The global criteria ALWAYS includes a geospatial area, and the subqueries MAY or MAY NOT contain geospatial areas.
I’ve noticed that as long as one subquery does not contain a geospatial area, this pattern works, but if all of the subqueries have a geospatial area, then the query fails due to the 2dsphere index throwing an “Intersection requires two index intervals” error.