Implicit $or for fields of the same name

[Joss_Markham]

The M001 course reiterates that mongo uses an implicit $and operator for search queries.

ie:

Implicitly, a logical AND conjunction connects the clauses of a compound query so that the query selects the documents in the collection that match all the conditions.
source

However, this does not seem to be the case when the fieldnames are the same. ie

{ status: "A", status: "B" }

…actually corresponds to the following SQL statement:

SELECT * FROM inventory WHERE status = "A" OR status = "B"

Maybe this is obvious here, but when querying arrays with the implicit “field contains at least *one* element with the specified value” (source) syntax, I was expecting:

db.inventory.find( { tags: "red", tags: "blank" } )

…to correspond to the following SQL statement:

SELECT * FROM inventory WHERE tags IN ("red") AND tags IN ("blank")

…but of course it doesn’t.

I dont mean to be pedantic but perhaps this could be clearer in the docmentation?

[santimir (santi)]

Welcome @Joss_Markham,

Interesting question. Problem here is you’re querying using JSON documents. There can only be a single field with a particular name.

Instead of erroring out, Mongo sends the query with the last repeated field in the document.

Check by yourself the output of:

db.collName.find({ status: "A", status: "B" }).explain()

You should use either $nin, $in, $all and that sort of operators.

---

[Joss_Markham]

Ahhh - I didn’t notice it was only using the last repeat

Many thanks for the quick reply

[Stennie]

@santimir

Instead of erroring out, Mongo sends the query with the last repeated field in the document.

Hi @santimir,

To be clear, this is a limitation of languages representing a JSON object as an hash or dictionary rather than how MongoDB processes a query. The JSON specs (eg RFC 8259 - The JavaScript Object Notation (JSON) Data Interchange Format) suggest “the names within an object SHOULD be unique” (not MUST be unique), leading to some interesting variations in parser behaviour. It is up to the JSON parser implementation to decide how to handle duplicate fields and field order in a JSON object: take the last value set, take the first value set, throw an exception, allow duplicate fields, or consider this undefined behaviour.

In the node shell (no MongoDB involved) you can observe the last value set for a repeated field name is what remains:

Welcome to Node.js v16.14.0.
Type ".help" for more information.
> { status: "A", status: "B" }
{ status: 'B' }

MongoDB stores documents as BSON, which has richer type support than JSON and has some notable differences (for example, BSON documents are ordered binary objects, not dictionaries).

For some related background, see Request for explanation! -- Chapter 4: Query Operators Lecture - #12 by Stennie

Regards,
Stennie

[santimir (santi)]

Hi @Stennie

That’s interesting, will follow your lessons from 1 onwards if available

Actually, I never got BSON very well either.

Thanks