Mongo: How to retrieve the document exactly matching a field whose type may be a number or array?

[sammy Ma]

This is my operation:

> db.foo.find({"extraid": 123})

{ "_id" : ObjectId("5e4e3e068438c24cdec0eeb9"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123, "abc" ] }

{ "_id" : ObjectId("5e4e3e078438c24cdec0eeba"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123, "abc", "efg" ] }

{ "_id" : ObjectId("5e4e3e078438c24cdec0eebc"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123 ] }

{ "_id" : ObjectId("5e4e3e078438c24cdec0eebd"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : 123 }

But the result I expect to get is only:

{ "_id" : ObjectId("5e4e3e078438c24cdec0eebd"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : 123 }

I have tried operator "$eq" :

> db.foo.find({"extraid": {"$eq": 123}})

{ "_id" : ObjectId("5e4e3e068438c24cdec0eeb9"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123, "abc" ] }

{ "_id" : ObjectId("5e4e3e078438c24cdec0eeba"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123, "abc", "efg" ] }

{ "_id" : ObjectId("5e4e3e078438c24cdec0eebc"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123 ] }

{ "_id" : ObjectId("5e4e3e078438c24cdec0eebd"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : 123 }

The result is still not my expectation. So who knows match type exactly? Thanks in advance!

[sammy Ma]

I also try to run $type operator so that it can match exactly, but the result is still not I want:

> db.foo.find({"extraid": {"$eq": 123, "$type":"int"}})

{ "_id" : ObjectId("5e4e3e068438c24cdec0eeb9"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123, "abc" ] }
{ "_id" : ObjectId("5e4e3e078438c24cdec0eeba"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123, "abc", "efg" ] }
{ "_id" : ObjectId("5e4e3e078438c24cdec0eebc"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123 ] }
{ "_id" : ObjectId("5e4e3e078438c24cdec0eebd"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : 123 }

> db.foo.find({"extraid": {"$eq": 123, "$type":"array"}})

{ "_id" : ObjectId("5e4e3e068438c24cdec0eeb9"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123, "abc" ] }
{ "_id" : ObjectId("5e4e3e078438c24cdec0eeba"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123, "abc", "efg" ] }
{ "_id" : ObjectId("5e4e3e078438c24cdec0eebc"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : [ 123 ] }
>

$type: array can match all array type of 'extraid', but $type: int is not work.

在 2020年2月21日星期五 UTC+8上午9:45:35，sammy Ma写道：

[Vipin Kumar]

Hi Sammy,

There are multiple ways I feel. Here is a simple one I may try,

This ‘extraid’ being a single dimension array, 

You have to $unwind the array and then match. 

db.food.find(

{$unwind: '$extraid'},

{$match:{"extraid": 123}}

);

You can further use aggregate/group/project stages to consolidate as needed.

I hope it helps.

Thanks and Regards

Vipin Kumar

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/5ced83da-ff00-475d-baa0-f4824d4eaace%40googlegroups.com.

[Prasad Saya]

Hello,

The following query will get only one document, where "extraid": 123

db.foo.find( { $expr: { $eq: [ { $type: "$extraid" }, "double" ] } } )

- Note that a number, by default, is a double (not an int) in MongoDB documents.

- To find what the type of a field is, you can try this aggregation query:

db.foo.aggregate( [
    { $addFields: { fld_type: { $type: "$extraid" } } }
] ).pretty()

 - Prasad

[sammy Ma]

Thank you Prasad. But this command will return nothing, and this number actually is a `int`.

在 2020年2月21日星期五 UTC+8下午1:10:14，Prasad Saya写道：

[sammy Ma]

Thank you Vipin. 

$unwind will unfold the 'extraid' array, and this query will return four documents, but I only want one document with _id is ObjectId("5e4e3e078438c24cdec0eebd") like below:

{ "_id" : ObjectId("5e4e3e078438c24cdec0eebd"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : 123 }

 

在 2020年2月21日星期五 UTC+8下午12:30:16，Vipin Kumar写道：

To unsubscribe from this group and stop receiving emails from it, send an email to mongod...@googlegroups.com.

[sammy Ma]

Hi Prasad, I slightly modified the statement you provided, and successfully got the results I wanted, thank you very much!

This is my statement and returned result:

> db.bar.find( { $expr: {$and: [{ $eq: [ { $type: "$extraid" }, "int"] }, { $eq: ["$extraid", 123] }]} } )

{ "_id" : ObjectId("5e4e3e078438c24cdec0eebd"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : 123 }

>

在 2020年2月21日星期五 UTC+8下午1:10:14，Prasad Saya写道：

Hello,

[Vipin Kumar]

Hi Sammy , 

I didnt notice the only one match  part.

Great to know , you got it solved. 

 

Just of out curiosity , can you see this returns you the result as well?

db.foo.find({$and:[{"extraid":{$not:{$type:"array"}}},{"extraid": 123}]}) 

Tx

Vipin Kumar 

To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/71deb34f-7ed4-40a9-b3f4-733a2d411aa6%40googlegroups.com.

[sammy Ma]

Hi Vipin, your query statement also works! Thank for your help.

> db.foo.find({$and:[{"extraid":{$not:{$type:"array"}}},{"extraid": 123}]})

{ "_id" : ObjectId("5e4e3e078438c24cdec0eebd"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : 123 }

>

在 2020年2月21日星期五 UTC+8下午6:48:16，Vipin Kumar写道：

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/71deb34f-7ed4-40a9-b3f4-733a2d411aa6%40googlegroups.com.

[sammy Ma]

Hello Prasad, can you help me to see below query:

> db.bar.find( { $expr: {$and: [{ $eq: [ { $type: "$extraid" }, "number"] }, { $eq: ["$extraid", 123] }]} } )

> db.bar.find( { $expr: {$and: [{ $eq: [ { $type: "$extraid" }, "int"] }, { $eq: ["$extraid", 123] }]} } )

{ "_id" : ObjectId("5e4f97aceb649645a42d7918"), "from" : "0xabsdfeeqwsfdasfafaefegr", "to" : "0xfdshshrtfafdfadfaete", "extraid" : 123 }

According to description on MongoDB website, $type supports the "number" alias, which will match against the following BSON types:

• double
• 32-bit integer
• 64-bit integer
• decimal

It is confused that when I assign "int" to $type, I can get the result, but "number" is not.

在 2020年2月21日星期五 UTC+8下午1:10:14，Prasad Saya写道：

Hello,

[Prasad Saya]

Hello Sammy.

Yes, your observation is correct; it will not work. Because, the query is using the aggregation $type operator, not the find query $type operator. Note the difference in the linked documentation. What you noted about the "number" is, I think, relevant with the query $type operator only.

- Prasad.

[sammy Ma]

Yes, you are right. On the documentation of aggregation $type operator, I have not found the description about "number" alias.

Thanks for your help.

Prasad Saya <googu...@gmail.com> 于2020年2月22日周六 上午10:48写道：

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/32760905-c709-4e1e-8dff-849d34dfffd6%40googlegroups.com.