Quering in a array/list in CouchDB

Question

I do not know how to implement the following query in CouchDB:

chr.letter between two objects/dicts in chr are not the same and no X, and
the document must be in range 200000 - 2000000, but I guess it has to be done with startkey and endkey in the view.

The example output could look like this:

{"_id":"7", "sub_name":"B01", "name":"A", "pos":828288, "s_type":1}
{"_id":"8", "sub_name":"B01", "name":"A", "pos":171878, "s_type":3} will not meet range condition in the view
{"_id":"9", "sub_name":"B01", "name":"A", "pos":871963, "s_type":3}

Document 10 is not valid, because chr.no = 6 has chr.letter = X. And document 14 is not valid, because chr.no = 5 and chr.no = 6 both have the same chr.letter = G

The database contains following documents:

{
  "_id":"10",
  "_rev":"3-5288068d2c4ef3e6a9d3f8ff4e3377dd",
  "sub_name":"B01",
  "name":"A",
  "pos":1932523,
  "s_type":1,
  "chr":[
      {
        "letter":"T",
        "no":4
      },
      {
        "letter":"A",
        "no":5
      },
      {
        "letter":"X",
        "no":6
      }
  ],
  "type":"Test"
}{
  "_id":"14",
  "_rev":"3-21300d06c31224416b8ff71b71b304d8",
  "sub_name":"B01",
  "name":"A",
  "pos":667214,
  "s_type":1,
  "chr":[
      {
        "letter":"T",
        "no":4
      },
      {
        "letter":"G",
        "no":5
      },
      {
        "letter":"G",
        "no":6
      }
  ],
  "type":"Test"
}{
  "_id":"7",
  "_rev":"2-1516ba547bdd21724158bc854f39f66b",
  "sub_name":"B01",
  "name":"A",
  "pos":828288,
  "s_type":1,
  "chr":[
      {
        "letter":"C",
        "no":5
      },
      {
        "letter":"T",
        "no":6
      }
  ],
  "type":"Test"
}{
  "_id":"8",
  "_rev":"2-750078ccc9e74616f33a2537e41b8414",
  "sub_name":"B01",
  "name":"A",
  "pos":171878,
  "s_type":3,
  "chr":[
      {
        "letter":"C",
        "no":5
      },
      {
        "letter":"T",
        "no":6
      }
  ],
  "type":"Test"
}{
  "_id":"9",
  "_rev":"2-3d68352a2d98c56fd322ae674fb7c38a",
  "sub_name":"B01",
  "name":"A",
  "pos":871963,
  "s_type":3,
  "chr":[
      {
        "letter":"A",
        "no":5
      },
      {
        "letter":"G",
        "no":6
      }
  ],
  "type":"Test"
}

The above database has been created with the below script:

import couchdb

# $ sudo systemctl start couchdb
# http://localhost:5984/_utils/

server = couchdb.Server()
db = server.create("test")
# except couchdb.http.ResourceConflict:
#db = server["test"]

r = [["Test", "A", "B01", 828288,  1,    7, 'C', 5],
    ["Test", "A", "B01", 828288,  1,    7, 'T', 6],
    ["Test", "A", "B01", 171878,  3,    8, 'C', 5],
    ["Test", "A", "B01", 171878,  3,    8, 'T', 6],
    ["Test", "A", "B01", 871963,  3,    9, 'A', 5],
    ["Test", "A", "B01", 871963,  3,    9, 'G', 6],
    ["Test", "A", "B01", 1932523, 1,   10, 'T', 4],
    ["Test", "A", "B01", 1932523, 1,   10, 'A', 5],
    ["Test", "A", "B01", 1932523, 1,   10, 'X', 6],
    ["Test", "A", "B01", 667214,  1,   14, 'T', 4],
    ["Test", "A", "B01", 667214,  1,   14, 'G', 5],
    ["Test", "A", "B01", 667214,  1,   14, 'G', 6]]

# _id = None
for i in r:
    _id = str(i[5])
    doc = db.get(_id)
    if doc is None:
        doc = {
            'type': i[0],
            'name': i[1],
            'sub_name': i[2],
            'pos': i[3],
            's_type': i[4],
            '_id': _id,
            'chr':[]
        }

        doc['chr'].append({
            "letter":i[6],
            "no":i[7]
        })

    else:
        doc['chr'].append({
            "letter":i[6],
            "no":i[7]
        })

    db.save(doc)

How is it possible to implement the above query or does the documents structure has to be change to make the query possible?

your above sample code with `r` will create duplicate `_id`, are you sure this is the case? — Anzel, Oct 17 '14 at 11:58
The script avoid duplication and only append and object/dict to `chr` in case of duplication. — user977828, Oct 17 '14 at 13:06

score 0 · Answer 1 · edited May 23 '17 at 12:05

Solution thanks to https://stackoverflow.com/a/26436244/977828

function(doc) {
    var judge = function (doc) {
        var unexpect = "X";
        var letter1 = unexpect, letter2 = unexpect;
        for (var i in doc.chr) {
            var chr = doc.chr[i];
            if (chr.no == 5) {
                letter1 = chr.letter;
            } else if (chr.no == 6) {
                letter2 = chr.letter;
            }
        }
        if (letter1 != letter2 && letter1 != unexpect && letter2 != unexpect) {
            return true;
        }
        return false;
    }; 
    if (judge(doc)) {
      emit(doc);
    }
}

Quering in a array/list in CouchDB

1 Answers1