1

I have a collection "films" with the following fields :

_id Actors Category Description Length Rating Rental Duration Replacement Cost Special Features Title

Actors is an array with three fields

"First name" "Last name" "actorId"

Sample documents from films

{"_id":1,
"Actors":[{"Last name":"GUINESS",
"actorId":1,
"First name":"PENELOPE"},
{"First name":"CHRISTIAN",
"Last name":"GABLE",
"actorId":10},
{"First name":"LUCILLE",
"Last name":"TRACY",
"actorId":20},
{"actorId":30,
"First name":"SANDRA",
"Last name":"PECK"},
{"First name":"JOHNNY",
"Last name":"CAGE",
"actorId":40},
{"First name":"MENA",
"Last name":"TEMPLE",
"actorId":53},
{"First name":"WARREN",
"Last name":"NOLTE",
"actorId":108},
{"First name":"OPRAH",
"Last name":"KILMER",
"actorId":162},
{"First name":"ROCK",
"Last name":"DUKAKIS",
"actorId":188},
{"First name":"MARY",
"Last name":"KEITEL",
"actorId":198}],
"Category":"Documentary",
"Description":"A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies",
"Length":"86",
"Rating":"PG",
"Rental Duration":"6",
"Replacement Cost":"20.99",
"Special Features":"Deleted Scenes,
Behind the Scenes",
"Title":"ACADEMY DINOSAUR"}
{"_id":2,
"Actors":[{"First name":"BOB",
"Last name":"FAWCETT",
"actorId":19},
{"First name":"MINNIE",
"Last name":"ZELLWEGER",
"actorId":85},
{"First name":"SEAN",
"Last name":"GUINESS",
"actorId":90},
{"First name":"CHRIS",
"Last name":"DEPP",
"actorId":160}],
"Category":"Horror",
"Description":"A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China",
"Length":"48",
"Rating":"G",
"Rental Duration":"3",
"Replacement Cost":"12.99",
"Special Features":"Trailers,
Deleted Scenes",
"Title":"ACE GOLDFINGER"}

I want 1/ to create a text index the actors fields along with "Category" and "Description" 2/ to search for an actor first name or last name and find it.

I used this

db.films.createIndex(
   {
     Category: "text",
     Description: "text",
     "Actors.'First name'": "text",
     "Actors.'Last name'": "text"
   },
   {
     weights: {
       Category: 5,
       Description: 5,
       "Actors.'First name'":20,
       "Actors.'Last name'":20
     },
     name: "ix_text"
   }
 )

When I query the collection 

db.films.find( { $text: { $search: "SANDRA" } } ).pretty()

I get no result, although "SANDRA" exists in the Actors array

What is wrong? My index, my query, or both ?

Tarek EZZAT
  • 333
  • 1
  • 5
  • 15
  • 1
    The index is wrong. It should be like `"Actors.First Name": "text"`. You have extra quotes around the names with spaces in them that you don't need. This should actually be a lesson NOT to use spaces in field names for a database. General programming convention is "camel case" i.e `"firstName"`. But basically get rid of the `''` chars since they don't belong in there. Drop indexes and start again. – Neil Lunn May 13 '18 at 10:18
  • Thanks for the tip, and thanks for the "lesson". Actually I use a JSON version of Sakila MySQL database, and I did not choose the index. I will post a question about how to rename the keys in a document, if I don't find a solution by myself – Tarek EZZAT May 13 '18 at 10:49
  • Please don't post that question: [How can I rename a field for all documents in MongoDB?](https://stackoverflow.com/questions/9254351/how-can-i-rename-a-field-for-all-documents-in-mongodb) since it's clearly been answered before. Actually any [sane search term I'm thinking of](https://www.google.com/search?q=mongodb+rename+fields) "should" return the MongoDB documentation for `$rename` as the top search result. If you search and don't find either of those then I really don't know how. Changing your field names would be the "recommended" course of action as it will make things easier later. – Neil Lunn May 13 '18 at 11:08

0 Answers0