Question about query..help~

Hello.
I have 6 documents.

> db.persons.find({})
{ "_id" : "1", "sports" : [ "Soccer" ] }
{ "_id" : "2", "sports" : "Soccer" }
{ "_id" : "3", "sports" : [ "Soccer", "Baseball" ] }
{ "_id" : "4", "sports" : [ "Football", "Baseball" ] }
{ "_id" : "5", "sports" : [ "Baseball", "Soccer" ] }
{ "_id" : "6", "sports" : [ "Soccer", "Football", "Baseball" ] }

when i use
db.person.find({“sports” : {$all : [“Soccer”, “Baseball”]}})

i got 3 documents

{ “_id” : “3”, “sports” : [ “Soccer”, “Baseball” ] }
{ “_id” : “5”, “sports” : [ “Baseball”, “Soccer” ] }
{ “_id” : “6”, “sports” : [ “Soccer”, “Football”, “Baseball” ] }.

Okay. i understand how $all works.
$all doesn’t care order.

i want to get

  1. index of Soccer > index of Baseball
  2. Soccer , Baseball. not any string between them.
    ( Football, Soccer, Baseball → ok)
    ( Soccer, Football, Baseball → no)
    is it possible in MongoDB???

Hi @Sungwon_Yoon,
It is possible with MongoDB, you can change your sports’ field to be a string like this:

> db.persons.find({})
{ "_id" : "1", "sports" :  ",Soccer," }
{ "_id" : "2", "sports" : ",Soccer," }
{ "_id" : "3", "sports" : ",Soccer,Baseball," }
{ "_id" : "4", "sports" : ",Football,Baseball," }
{ "_id" : "5", "sports" : ",Baseball,Soccer," }
{ "_id" : "6", "sports" : ",Soccer,Football,Baseball," }

Then you can get:

  1. index of Soccer > index of Baseball
  db.persons.find({sports: /,Baseball,.*Soccer,/})
  1. Soccer , Baseball. not any string between them.
  db.persons.find({sports: /,Soccer,Baseball,/})

Inspired by Model Tree Structures with Materialized Paths.

The problem with this solution is efficiency. If efficiency is important for your use case, you can keep the original array in addition to the string and filter first the documents that include the specified sports (without any additional constraints).

Goodluck,
Rafael,

Not a complete solution yet but a starting point.

It looks like you are using the positions of the items in the array to established some king of priority or ordering. I would be worry to depend on this. Personally, I would have add a priority field or something like that. Because it is more predictable and flexible.

This being said. The starting point of my solution is to use $map to determine the indexed of the wanted sports.

wanted_sports = [ "Soccer", "Baseball" ]
match = { "$match" : { "sports" : { "$all" , wanted_sports } } }
map = { "$map" : 
  { "input" : wanted_sports ,
    "as" : "wanted" ,
    "in" : { "$indexOfArray" : [ "$sports", "$$wanted" ] } } }
addField = { "$addField" : { "indexes" : map } }
c.aggregate( [ match , addField } )
// gives the temporary results
{ _id: '3', sports: [ 'Soccer', 'Baseball' ], indexes: [ 0, 1 ] }
{ _id: '5', sports: [ 'Baseball', 'Soccer' ], indexes: [ 1, 0 ] }
{ _id: '6',
  sports: [ 'Soccer', 'Football', 'Baseball' ],
  indexes: [ 0, 2 ] }
// next step still to figure out is to verify that indexes are consecutive and increasing