CODEWITHSUNDEEP
couchdb
artkoenig
artkoenig

Reputation: 7257

Multiple key search in CouchDB

Given the following object structure:

{
   key1: "...",
   key2: "...",
   data: "..."
}

Is there any way to get this object from a CouchDB by quering both key1 and key2 without setting up two different views (one for each key) like:

select * from ... where key1=123 or key2=123

Kind regards, Artjom

edit:

Here is a better description of the problem: The object described above is a serialized game state. A game has exactly one creator user (key1) and his opponent (key2). For a given user I would like to get all games where he is involved (both as creator and opponent).

Upvotes: 6

Views: 6195

Answers (4)

TonyW
TonyW

Reputation: 19

I also was struggling with simular question, how to use

"select * from ... where key1=123 or key2=123".

The following view would allow you to lookup customer documents by the LastName or FirstName fields: 

function(doc) {
  if (doc.Type == "customer") {
      emit(doc.LastName, {FirstName: doc.FirstName, Address: doc.Address});
      emit(doc.FirstName, {LastName: doc.LastName, Address: doc.Address});
  }
}

Upvotes: 1

publicmat
publicmat

Reputation: 851

I am using this for a web service that queries all my docs and returns every doc that matches both the existence of a node and the query. In this example I am using the node 'detail' for the search. If you would like to search a different node, you need to specify.

This is my first Stack Overflow post, so I hope I can help someone out :)

***Python Code

import tornado.httpserver
import tornado.ioloop
import tornado.options
import tornado.web
import httplib, json

from tornado.options import define,options

define("port", default=8000, help="run on the given port", type=int)


class MainHandler(tornado.web.RequestHandler):
    def get(self):
        db_host = 'YOUR_COUCHDB_SERVER'
        db_port = 5984
        db_name = 'YOUR_COUCHDB_DATABASE'

        node = self.get_argument('node',None)
        query = self.get_argument('query',None)

        cleared = None
        cleared = 1 if node else self.write('You have not supplied an object node.<br>')
        cleared = 2 if query else self.write('You have not supplied a query string.<br>')

        if cleared is 2:
            uri = ''.join(['/', db_name, '/', '_design/keysearch/_view/' + node + '/?startkey="' + query + '"&endkey="' + query + '\u9999"'])
            connection = httplib.HTTPConnection(db_host, db_port)
            headers = {"Accept": "application/json"}
            connection.request("GET", uri, None, headers)
            response = connection.getresponse()
            self.write(json.dumps(json.loads(response.read()), sort_keys=True, indent=4))

class Application(tornado.web.Application):
    def __init__(self):
        handlers = [
            (r"/", MainHandler)
        ]
        settings = dict(
            debug = True
        )
        tornado.web.Application.__init__(self, handlers, **settings)

def main():
    tornado.options.parse_command_line()
    http_server = tornado.httpserver.HTTPServer(Application())
    http_server.listen(options.port)
    tornado.ioloop.IOLoop.instance().start()

if __name__ == '__main__':
    main()


***CouchDB Design View
{
   "_id": "_design/keysearch",
   "language": "javascript",
   "views": {
       "detail": {
           "map": "function(doc) { var docs = doc['detail'].match(/[A-Za-z0-9]+/g); if(docs) { for(var each in docs) { emit(docs[each],doc); } } }"
       }
   }
}

Upvotes: 0

Marcello Nuccio
Marcello Nuccio

Reputation: 3901

Emit both keys (or only one if equal):

function(doc) {
  if (doc.hasOwnProperty('key1')) {
    emit(doc.key1, 1);
  }
  if (doc.hasOwnProperty('key2') && doc.key1 !== doc.key2) {
    emit(doc.key2, 1);
  }
}

Query with (properly url-encoded):

?include_docs=true&key=123

or with multiple values:

?include_docs=true&keys=[123,567,...]

UPDATE: updated to query multiple values with a single query.

Upvotes: 4

David V
David V

Reputation: 11699

You could create a CouchDB view which produces output such as:

["key1", 111],
["key1", 123],
["key2", 111],
["key2", 123],
etc.

It is very simple to write a map view in javascript:

function(doc) {
    emit(["key1", doc["key1"]], null);
    emit(["key2", doc["key2"]], null);
}

When querying, you can query using multiple keys:

{"keys": [["key1", 123], ["key2", 123]]}

You can send that JSON as the data in a POST to the view. Or preferably use an API for your programming language. The results of this query will be each row in the view that matches either key. So, every document which matches on both key1 and key2 will return two rows in the view results.

Upvotes: 2

Related Questions