MongoDB: query array within embedded document

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" : [ ] } 
  ] 
}

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.