Query nested objects in a document with unknown key

Hi,

I have a collection with many documents that do have a “item” property where the value is an object with key-value-pairs:

{
    "_id" : ObjectId("60054d3e20b5c978eb93bb7f"),
	"items": {
		"o1n2rpon12prn": {
			"LastEvent": "On"
		},
		"sdbqw12f12f": {
			"LastEvent": "Off"
		},
		"yxvcaqeg23g23g": {
			"LastEvent": "Error"
		}
	}
}

Now I try to query all objects that have an item with “LastEvent” being “On” but I can’t find a way how to do that.

I can find many examples to query nested arrays but not a dictionary like structure with key value pairs.
Only thing I found was aggregating and using objectToArray before matching but that sounds like a huge performance impact and it’s pretty hard to get done with C#. Is there no way to do something like “item.*.LastEvent”: “On”?

2 Likes

It will be hard to achieve something with the schema. I would consider using Building with Patterns: The Attribute Pattern | MongoDB Blog.

{
    "_id" : ObjectId("60054d3e20b5c978eb93bb7f"),
	"items": [
		{       "k" : "o1n2rpon12prn" ,
			"LastEvent": "On"
		},
		{    "k" : "sdbqw12f12f" ,
		    "LastEvent": "Off"
		},
		{     "k" : "yxvcaqeg23g23g",
		     "LastEvent": "Error"
		}
	]
}

You can then have a multi-keys index on items.k and with $elemMatch (or $unwind and $match) you can find the k for which LastEvent:On.

Just the names o1n2rpon12prn, sdbqw12f12f and yxvcaqeg23g23g gives me the feeling that they should be values rather than keys.

1 Like

Thank you for the answer :slight_smile:

The keys are unique and basicly the id of the item (in my case they were just examples but they are based on UUIDs in reality).

I was thinking about using an array and adding the key as an id field, but I found it hard to update the correct item later. I have multiple services that access the same database and add different values to the same “key” (sometimes even simultaneously). For example one adds/updates the “LastEvent” field and another service adds/updates a “CurrentState” field. Currently it works very easy by using an upsert and a simple set on “item.id.LastEvent” and the different services don’t interfere. If the key isn’t there yet it will just be created.
With arrays it seems to be much harder to upsert data into the array depending on a key or field inside the array.
I think I could do something similar for existing values by filtering for the item id and then using “items.$.LastEvent” to update that object, but the filter won’t work if there is no such object yet and with upsert it will just create a whole new document then.

On stackoverflow I found that question and the answers there is to use objects…but then I’m back here.
node.js - Can mongo upsert array data? - Stack Overflow

As multiple services might work on the same document I also can’t use two calls (pull and update).

:confused:

I also thought about using separate documents for the items but then my problem is that I’ve a retention policy set for this collection and I then need to figure out a way to remove the other documents aswell.

As a workaround I’m now using an aggregate pipeline similar to that:

[
   {$addFields:{u:{$objectToArray:"$items"}}},
   {$match:{"u":{$elemMatch: { "v.LastEvent": "On" }}}},
   {$project:{u:0}}
]

But I’m a bit worried about performance as I might have a huge count of documents

Hi @Wolfspirit,

Have you considered using a text index where you will index all items like { 'items.$**' : "text" }

Than query :

 db.stores.find( { $text: { $search: "On" } } )

Or aggregate with text search and later on filter with $map.

Thanks
Pavel

1 Like

Since I am not familiar with such indexes, I go ahead and try it.

I reach an hurdle at creating the index:

I got:

mongo localhost> db.search.createIndex( { 'items.$**' : "text" } )
{
	"ok" : 0,
	"errmsg" : "Index key contains an illegal field name: field name starts with '$'.",
	"code" : 67,
	"codeName" : "CannotCreateIndex"
}

Creating the index as given at https://docs.mongodb.com/manual/core/index-text/ worked:

mongo localhost> db.search.createIndex( { '$**' : "text" } )
2021-01-19T08:24:52.091-0500 I INDEX    [conn8] build index on: test.search properties: { v: 2, key: { _fts: "text", _ftsx: 1 }, name: "$**_text", ns: "test.search", weights: { $**: 1 }, default_language: "english", language_override: "language", textIndexVersion: 3 }
2021-01-19T08:24:52.091-0500 I INDEX    [conn8] 	 building index using bulk method; build may temporarily use up to 500 megabytes of RAM
2021-01-19T08:24:52.094-0500 I INDEX    [conn8] build index done.  scanned 1 total records. 0 secs
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

However, db.search.find( { $text: { $search: "On" } } ) gives no result despite:

mongo localhost> db.search.find().pretty()
{
	"_id" : ObjectId("60054d3e20b5c978eb93bb7f"),
	"items" : {
		"o1n2rpon12prn" : {
			"LastEvent" : "On"
		},
		"sdbqw12f12f" : {
			"LastEvent" : "Off"
		},
		"yxvcaqeg23g23g" : {
			"LastEvent" : "Error"
		}
	}
}

Both mongo and mongod are 4.0.5 on Arch Linux.

I guess I miss something.

Hi @steevej and @Wolfspirit,

Sorry I might confused Wild Card index syntax and text index and thought items.$** is acceptable.

Now @steevej, you have hit an edge case with your search for the word “on” . The problem is that this word is considered a stop word like “and”,“or” and “the” etc.

Therefore it is not indexed :man_facepalming:

If you remove the default english and specifiy “none” they will:

db.search.createIndex( { '$**' : "text" },{ default_language: "none" , language_override : "none"} )

Now it returns a result:

 db.search.aggregate([{$match : { $text: { "$search": "on" } }}])
{ "_id" : ObjectId("60054d3e20b5c978eb93bb7f"), "items" : { "o1n2rpon12prn" : { "LastEvent" : "On" }, "sdbqw12f12f" : { "LastEvent" : "Off" }, "yxvcaqeg23g23g" : { "LastEvent" : "Error" } } }

Please let me know if you have any additional questions.

Best regards,
Pavel

1 Like

Thanks, I learned a lot.

Hello Steevej, is there any way to search the key in your reference if the key is already know ?

any suggestion will save a day. :slight_smile:

I do not understand your question.

It is an old thread. Please start a new one.

Provide sample documents, the queries and expected results.

I am asking if we provide the text value for searching it will search and return the document . is there any way to find the key for ex below document :- is there any way to search o1n2rpon12prn value and return all the document which contains this value:- I am reading your last year previous post so thought I can ask here only. sorry for the statement, I am not a good writer.
{
“_id” : ObjectId(“60054d3e20b5c978eb93bb7f”),
“items” : {
“o1n2rpon12prn” : {
“LastEvent” : “On”
},
“sdbqw12f12f” : {
“LastEvent” : “Off”
},
“yxvcaqeg23g23g” : {
“LastEvent” : “Error”
}
}
}

db.collection.find( { "items.o1n2rpon12prn.LastEvent" : "On" } )

see MongoDB Courses and Trainings | MongoDB University

Availablefields(doc: any, keys: {}, prefix: string): any {
for (var key in doc) {
if (!(key in keys) && key !== “_id”) {
if (Array.isArray(doc[key])) {
// Include array key name
keys[prefix + key] = prefix + key;

      // Continue to iterate over array elements
      for (var i = 0; i < doc[key].length; i++) {
        if (typeof doc[key][i] === 'object') {
          this.Availablefields(doc[key][i], keys, prefix + key + '.' + i + '.');
        }
      }
    } else if (typeof doc[key] === "object") {
      this.Availablefields(doc[key], keys, prefix + key + ".");
    } else {
      keys[prefix + key] = prefix + key;
    }
  }
}
return keys;

}

} you can get unknown nested key object and nested array by this logic

  1. List item