title: Running advanced MongoDB queries in R with rmongodb
body: <p>As MySQL is driving me nuts I'm trying to make myself acquainted with my first "NoSQL" DBMS and it happened to be <a href="http://www.mongodb.org/" rel="nofollow">MongoDB</a>. I'm connecting to it via <a href="http://cran.r-project.org/web/packages/rmongodb/index.html" rel="nofollow">rmongodb</a>.</p>
  
  <p>The more I play around with <a href="http://cran.r-project.org/web/packages/rmongodb/index.html" rel="nofollow">rmongodb</a>, the more questions/problems come up with respect to running advanced queries. </p>
  
  <p>First I present some example data before I go into detail about the different types of queries that I can't seem to specify correctly.</p>
  
  <h2>Example Data</h2>
  
  <p>The example is taken from the <a href="http://www.mongodb.org/display/DOCS/Schema+Design" rel="nofollow">MongoDB website</a> and has been simplified a bit.</p>
  
  <pre><code>pkg &lt;- "rmongodb"
  if (!require(pkg, character.only=TRUE)) {
      install.packages(pkg)
      require(pkg, character.only=TRUE)   
  }
  
  # Connect to DB
  db &lt;- "test"
  ns &lt;- "posts"
  mongo &lt;- mongo.create(db=db)
  
  # Insert document to collection 'test.users'
  b &lt;- mongo.bson.from.list(list(
      "_id"="alex", 
      name=list(first="Alex", last="Benisson"),
      karma=1.0,
      age=30,
      test=c("a", "b")
  ))
  mongo.insert(mongo, "test.users", b)
  
  # Insert document to collection 'test.posts'
  b &lt;- mongo.bson.from.list(list(
          "_id"="abcd",
          when=mongo.timestamp.create(strptime("2011-09-19 02:00:00",
              "%Y-%m-%d %H:%M:%s"), increment=1),
          author="alex",
          title="Some title",
          text="Some text.",
          tags=c("tag.1", "tag.2"),
          votes=5,
          voters=c("jane", "joe", "spencer", "phyllis", "li"),
          comments=list(
              list(
                  who="jane", 
                  when=mongo.timestamp.create(strptime("2011-09-19 04:00:00",
                      "%Y-%m-%d %H:%M:%s"), increment=1),
                  comment="Some comment."
              ),
              list(
                  who="meghan", 
                  when=mongo.timestamp.create(strptime("2011-09-20 13:00:00",
                      "%Y-%m-%d %H:%M:%s"), increment=1),
                  comment="Some comment."
              )
          )
      )
  )
  b
  mongo.insert(mongo, "test.posts", b)
  </code></pre>
  
  <p>Two questions related to inserting JSON/BSON objects:</p>
  
  <ol>
  <li>Document 'test.posts', field <code>voters</code>: is it correct to use <code>c()</code> in this case?</li>
  <li>Document 'test.posts', field <code>comments</code>: what's the right way to specify this, <code>c()</code> or <code>list()</code>?</li>
  </ol>
  
  <h2>Top Level Queries: they work a treat</h2>
  
  <p>Top level queries work just fine:</p>
  
  <pre><code># Get all posts by 'alex' (only titles)
  res &lt;- mongo.find(mongo, "test.posts", query=list(author="alex"), 
      fields=list(title=1L))
  out &lt;- NULL
  while (mongo.cursor.next(res))
      out &lt;- c(out, list(mongo.bson.to.list(mongo.cursor.value(res))))
  
  &gt; out
  [[1]]
                         _id                      title 
                       "abcd"            "No Free Lunch" 
  </code></pre>
  
  <h2>Question 1: Basic Sub Level Queries</h2>
  
  <p>How can run a simple "sub level queries" (as opposed to top level queries) that need to reach into arbitrarily deep sublevels of a <a href="http://bsonspec.org/" rel="nofollow">JSON/BSON</a> style MongoDB object? These sub level queries make use of MongoDB's <a href="http://www.mongodb.org/display/DOCS/Dot+Notation+%28Reaching+into+Objects%29" rel="nofollow">dot notation</a> and I can't seem to figure out how to map that to a valid <a href="http://cran.r-project.org/web/packages/rmongodb/index.html" rel="nofollow">rmongodb</a> query</p>
  
  <p>In plain MongoDB syntax, something like</p>
  
  <pre><code>&gt; db.posts.find( { comments.who : "meghan" } )
  </code></pre>
  
  <p>would work. But I can't figure out how to do that with <a href="http://cran.r-project.org/web/packages/rmongodb/index.html" rel="nofollow">rmongodb</a> functions</p>
  
  <p><strong>Here's what I tried so far</strong></p>
  
  <pre><code># Get all comments by 'meghan' from 'test.posts'
  
  #--------------------
  # Approach 1)
  #--------------------
  res &lt;- mongo.find(mongo, "test.posts", query=list(comments=list(who="meghan")))
  out &lt;- NULL
  while (mongo.cursor.next(res))
      out &lt;- c(out, list(mongo.bson.to.list(mongo.cursor.value(res))))
  
  &gt; out
  NULL
  # Does not work
  
  #--------------------
  # Approach 2) 
  #--------------------
  buf &lt;- mongo.bson.buffer.create()
  mongo.bson.buffer.start.object(buf, "comments")
  mongo.bson.buffer.append(buf, "who", "meghan")
  mongo.bson.buffer.finish.object(buf)
  query &lt;- mongo.bson.from.buffer(buf)
  res &lt;- mongo.find(mongo, "test.posts", query=query)
  out &lt;- NULL
  while (mongo.cursor.next(res))
      out &lt;- c(out, list(mongo.bson.to.list(mongo.cursor.value(res))))
  
  &gt; out
  NULL
  # Does not work
  </code></pre>
  
  <h2>Question 2: Queries Using <code>$</code> Operators</h2>
  
  <p>These work</p>
  
  <p><strong>Query 1</strong></p>
  
  <pre><code>buf &lt;- mongo.bson.buffer.create()
  mongo.bson.buffer.start.object(buf, "age")
  mongo.bson.buffer.append(buf, "$lte", 30)
  mongo.bson.buffer.finish.object(buf)
  criteria &lt;- mongo.bson.from.buffer(buf)
  criteria
  
  &gt; mongo.find.one(mongo, "test.users", query=criteria)
      _id : 2      alex
      name : 3     
          first : 2    Alex
          last : 2     Benisson
  
      karma : 1    1.000000
      age : 1      30.000000
      test : 4     
          0 : 2    a
          1 : 2    b
  </code></pre>
  
  <p><strong>Query 2</strong></p>
  
  <pre><code>buf &lt;- mongo.bson.buffer.create()
  mongo.bson.buffer.start.object(buf, "test")
  mongo.bson.buffer.append(buf, "$in", c("a", "z"))
  mongo.bson.buffer.finish.object(buf)
  criteria &lt;- mongo.bson.from.buffer(buf)
  criteria
  mongo.find.one(mongo, "test.users", query=criteria)
  </code></pre>
  
  <p>However, notice that an atomic set will result in a return value of <code>NULL</code></p>
  
  <pre><code>mongo.bson.buffer.append(buf, "$in", "a")
  # Instead of 'mongo.bson.buffer.append(buf, "$in", c("a", "z"))'
  </code></pre>
  
  <p>Trying the same with sub level queries I'm lost again</p>
  
  <pre><code>buf &lt;- mongo.bson.buffer.create()
  mongo.bson.buffer.start.object(buf, "name")
  mongo.bson.buffer.start.object(buf, "first")
  mongo.bson.buffer.append(buf, "$in", c("Alex", "Horst"))
  mongo.bson.buffer.finish.object(buf)
  mongo.bson.buffer.finish.object(buf)
  criteria &lt;- mongo.bson.from.buffer(buf)
  criteria &lt;- mongo.bson.from.buffer(buf)
  &gt; criteria
      name : 3     
          first : 3    
              $in : 4      
                  0 : 2    Alex
                  1 : 2    Horst
  
  &gt; mongo.find.one(mongo, "test.users", query=criteria)
  NULL
  </code></pre>