Find a value in multiple nested fields with the same name in MongoDB

Question

In some documents I have a property with a complex structure like:

{
  content: {
    foo: {
      id: 1,
      name: 'First',
      active: true 
    },
    bar: {
      id: 2,
      name: 'Second',
      active: false 
    },
    baz: {
      id: 3,
      name: 'Third',
      active: true 
    },
}

I'm trying to make a query that can find all documents with a given value in the field name across the different second level objects foo, bar, baz

I guess that a solution could be:

db.getCollection('mycollection').find({ $or: [
    {'content.foo.name': 'First'},
    {'content.bar.name': 'First'},
    {'content.baz.name': 'First'}
]})

But a I want to do it dynamic, with no need to specify key names of nested fields, nether repeat the value to find in every line.

If some Regexp on field name were available , a solution could be:

db.getCollection('mycollection').find({'content.*.name': 'First'}) // Match
db.getCollection('mycollection').find({'content.*.name': 'Third'}) // Match
db.getCollection('mycollection').find({'content.*.name': 'Fourth'}) // Doesn't match

Is there any way to do it?

Tom Slabbaert · Accepted Answer · 2022-09-28 12:03:36Z

1

I would say this is a bad schema if you don't know your keys in advance. Personally I'd recommend to change this to an array structure.

Regardless what you can do is use the aggregation $objectToArray operator, then query that newly created object. Mind you this approach requires a collection scan each time you execute a query.

db.collection.aggregate([
  {
    $addFields: {
      arr: {
        "$objectToArray": "$content"
      }
    }
  },
  {
    $match: {
      "arr.v.name": "First"
    }
  },
  {
    $project: {
      arr: 0
    }
  }
])

Mongo Playground

Another hacky approach you can take is potentially creating a wildcard text index and then you could execute a $text query to search for the name, obviously this comes with the text index/query limitations and might not be right for your usecase.

answered Sep 28, 2022 at 12:03

Tom Slabbaert

22.5k10 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gabriel Rodríguez Flores Over a year ago

The schema has been designed to directly retrieve objects within content without the need to search by value whether it exists or not. These keys are defined in the model, they are at most 4 and are optional, that's why this structure, because it is faster and easier to manipulate... until now...

Collectives™ on Stack Overflow

Find a value in multiple nested fields with the same name in MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related