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”?

It will be hard to achieve something with the schema. I would consider using https://www.mongodb.com/blog/post/building-with-patterns-the-attribute-pattern.

{
    "_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.

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.