CODEWITHSUNDEEP
rubymongodbmongoid
Miguel Ping
Miguel Ping

Reputation: 18347

MongoDB: query array within embedded document

I have the following doc definition (it's ruby)

class Block
  include Mongoid::Document

  field :index, type: Integer  # index 0,1,..  
  field :codes, type: Array    #[A, B, C,... ]

  embedded_in :Video
end

class Video
  include Mongoid::Document

  field :name, type: String
  embeds_many :blocks, :order => :index.asc
end

I want to query matching the property video.blocks.codes, but it is an array property of an embedded doc. I mainly want to do two types of queries:

Here's an example of the data I'm trying to match:

video#1
blocks: [{index: 1, codes:["a","g","c"]}, {index: 2, codes: [] }]

video#2
blocks: [{index: 1, codes:["x","b","d", "e"]}, {index: 2, codes: ["x","b"] }]

For example, I want to know how many blocks are there without non-empty codes array (answer is three blocks), and how many blocks are there with a b in the second position(index 1) (answer is there are two).

I'm using the mongoid driver so ideally the query would use the driver, but plain mongo is fine. Thanks!

Upvotes: 0

Views: 1918

Answers (2)

Rob Moore
Rob Moore

Reputation: 3383

I think what you are looking for is dot notation: http://docs.mongodb.org/manual/reference/glossary/#term-dot-notation

Question 1:

  • How many blocks exist with a non-null/non-empty codes array?
db.videos.find( { 'video.blocks.codes.0' : { $exists : true } } )

Effectively does the zero-th element of the array exist. For speed you create an index on video.blocks.codes. Also note that you will get back all of the video documents with at least 1 non-empty codes array within a block. To count the blocks you will have to do client side processing to remove the extra blocks.

Question 2:

  • How many blocks exist where the codes array matches a certain string in a given position?

Very similar answer. For a position 3:

db.videos.find( { 'video.blocks.codes.3' : 'the magic code' } )

Sorry I don't know Mongoid but hopefully you can translate the above.

HTH - Rob.

Edit:

This doesn't work, because blocks is embedded and codes is an array within blocks.

I don't think I understand the question then. The shell returns What I expect.

Example from the shell (reformatted) - First the data:

> db.test.find()
{ "_id" : ObjectId("51b7cfff0ccc6eb8b11c82b1"), 
  "blocks" : [    
     { "index" : 1, "codes" : [  "a", "g", "c" ] },
     { "index" : 2, "codes" : [ ] } 
  ] 
}
{ "_id" : ObjectId("51b7d0300ccc6eb8b11c82b2"), 
  "blocks" : [
     { "index" : 1, "codes" : [ "x", "b", "d", "e" ] },        
     { "index" : 2, "codes" : [ "x", "b" ] } 
  ] 
}
{ "_id" : ObjectId("51b7d0a50ccc6eb8b11c82b3"), 
  "blocks" : [ 
     { "index" : 1, "codes" : [ ] } 
  ] 
}

First Query: Find all documents with a block with at least 1 code:

> db.test.find( { 'blocks.codes.0' : { $exists : true } } )
{ "_id" : ObjectId("51b7cfff0ccc6eb8b11c82b1"), 
  "blocks" : [
     { "index" : 1, "codes" : [ "a", "g", "c" ] },
     { "index" : 2, "codes" : [ ] } 
   ] 
}
{ "_id" : ObjectId("51b7d0300ccc6eb8b11c82b2"), 
  "blocks" : [ 
     { "index" : 1, "codes" : [ "x", "b", "d", "e" ] },
     { "index" : 2, "codes" : [ "x", "b" ] } 
   ] 
}

Second Query: Find all documents where the n'th code is a specific value. In this case I chose the second (index 1) is 'g'.

> db.test.find( { 'blocks.codes.1' : "g" } )
{ "_id" : ObjectId("51b7cfff0ccc6eb8b11c82b1"), 
  "blocks" : [ 
     { "index" : 1, "codes" : [ "a", "g", "c" ] },
     { "index" : 2, "codes" : [ ] } 
  ] 
}

Upvotes: 1

Matt
Matt

Reputation: 17649

IMHO Block should be a separate collection with an additional attribute num_codes (and not embedded, code untested).

class Video
    include Mongoid::Document
    has_many :blocks
end

class Block
    include Mongoid::Document
    belongs_to :video
    field :index
    field :num_codes
    field :codes

    # warning pseudo code ahead:
    before_save :update_codes
    def update_codes
       # set num_codes to length of codes
       # delete all codes belonging to this block and recreate them
    end
end

To query for empty blocks: Blocks.where(num_codes : 0). This solves requirement 1.

Regarding requirement 2: As far as I know, MongoDB does not allow you to query for values at a specific index within an array (altough I might be wrong on this one). Again my suggestion would be to make a separate collection (code untested):

class Code
   include Mongoid::Document
   belongs_to :block
   field :position
   field :value
end

Code.where(position : 3, value : 'x')

Saving a video will thus take about 2-n insertions depending on the size of codes. But the collections are indexable ([:num_codes] for Blocks and [:position, :value] for Code) and should give you reasonable query performance - even for large collections. Hope that helps.

Upvotes: 0

Related Questions