Databases
StackOverflow2013
Postsdbo
PORunning advanced MongoDB queries in R with rmongodb
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORunning advanced MongoDB queries in R with rmongodb
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USStennie
    1. USRappster
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORunning advanced MongoDB queries in R with rmongodb
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORunning advanced MongoDB queries in R with rmongodb
      1. USRoman Kuzmin
      1. VTFavorite
    3. VO
      singulars
      1. PORunning advanced MongoDB queries in R with rmongodb
      1. USRappster
      1. VTFavorite
    1. COThe author (Gerald Lindsly) posted a short blog here - http://www.r-bloggers.com/rmongodb-r-driver-for-mongodb/. It's a little old but he seems to be keen for feedback so it could be worth dropping him an email (his address is a the bottom of the link)? AFAIK the driver is one of the community developed ones.
      singulars
      1. PORunning advanced MongoDB queries in R with rmongodb
      1. USMark Hillick
    2. COThanks Mark, I did scan through the post, but just saw that it has quite an extensive example application linked to. Right, it's community developed. I eventually did try to contact Gerald, but also thought that putting this at SO would be a good idea.
      singulars
      1. PORunning advanced MongoDB queries in R with rmongodb
      1. USRappster
    3. COCool, I'm having a look at it but I'm very new to R so I doubt I can help much. Let me know how you get on.
      singulars
      1. PORunning advanced MongoDB queries in R with rmongodb
      1. USMark Hillick
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload