mongoDB:忽略空字段的多值字段搜索 [英] mongoDB : multi value field search ignoring null fields

问题描述

我正在尝试在几个字段中进行搜索，其中某些字段可以留空. 给定以下MongoDB中的Json文档(这里只有三个字段，但实际上是N个字段，N> 10):

I am trying to make a search on several field where some of them can be left blank. Given the following Json document in MongoDB (only three fields here, but N fields in reality, with N > 10) :

{
    'first_name' : 'value_X',
    'last_name' : 'value_Y',
    'mail_adress' : 'value_Z'

}

然后假设一个表单，用户可以在其中输入名字，姓氏，邮件地址和电话号码的值. 如果所有字段均已填写，则MongoDB中的查询如下所示:

Then let's suppose a form where the user can enter the value of the first name, last name, mail address and phone number. If all the field are filled the query in MongoDB looks like :

db.collection.find( {
                        'first_name' : 'value_A',
                        'last_name' : 'value_B',
                        'mail_adress' : 'value_C'

                    }
                )

我的问题是用户可能将某些字段留为空白: 这意味着我的查询也可以变成:

The problem I have is that I might have some fields left blank by the user : It means that my query can also become :

db.collection.find({ 'first_name' : 'value_A' })

或

db.collection.find( {'last_name' : 'value_B'})  

或

db.collection.find( {'mail_adress' : 'value_C'})

或

db.collection.find( {'first_name' : 'value_A','last_name' : 'value_B'})

或

db.collection.find( {'first_name' : 'value_A','last_name' : 'value_C'})

或

db.collection.find( {'last_name' : 'value_B','mail_adress' : 'value_C'})

我想知道是否需要为每个特定实例编写查询吗? 我还认为可以根据用户输入的字段动态生成查询，因为查询是Json文档，所以会很容易.

I would like to know if I need to write a query for each particular instance ? I also thought to generate query dynamically depending on the field entered by a user, it would be easy as query are Json doucment.

我还想知道MongoDB是否可以忽略字段是否为空(但我不想在查询中失去性能)

I am also wondering if MongoDB could ignore field if they are null (but I don't want to lose performance in my query)

任何人都可以帮助我吗?谢谢

Anyone can help me ? Thanks

在20140404下午6点进行答案的一部分

EDIT on 20140404 at 6PM : part of answer

以下是带有Aditya建议的Python代码. 我还按照svjn的建议添加了有关在前端生成查询的python代码:

Here is the Python code with Aditya suggestion. I also added the python code about generating the query in front end as svjn suggested:

'''
Created on 4 Apr 2014

@author: scoulombel
'''

import pymongo
from pymongo import MongoClient
import random
import string



# fill the database
def fillCollection(collection):
    doc =   {
      'first_name' : 'value_X',
      'last_name' : 'value_Y',
      'mail_adress' : 'value_Z'

    }

    docID = collection.insert(doc)

    for _ in range(0,10):
        doc = {
           'first_name' : ''.join(random.choice(string.ascii_uppercase) for _ in range(4)) + ''.join(random.choice(string.digits) for _ in range(4)),
           'last_name' : ''.join(random.choice(string.ascii_uppercase) for _ in range(4)) + ''.join(random.choice(string.digits) for _ in range(4)),
           'mail_adress' : ''.join(random.choice(string.ascii_uppercase) for _ in range(4)) + ''.join(random.choice(string.digits) for _ in range(4))

        }
        docID = collection.insert(doc)

# read data in a cursor
def readCursor(cursor):
    object_list = []
    count = 0   
    for doc in cursor :
        del doc["_id"]
        count += 1
        doc['resOrder'] =  count
        object_list.append(doc)

    print object_list

if __name__ == '__main__':  
    #client = MongoClient()
    client = MongoClient('ip', 'port')
    db = client.test_OR_AND_DB      
    collection = db.collection

    fillCollection(collection)

    # SEARCH VALUE which can be null or not    
    value_A = 'value_X'
    value_B = 'null' #'value_Y'
    value_C = 'null' #'value_Z'

    # APPROACH 1 : ADITYA answer for search
    cursor1 = db.collection.find(
                                {
                         '$or' : [
                                    { '$and' : [ {'first_name' : { '$ne': 'null' }}, {'first_name' : value_A } ]},
                                    { '$and' : [ {'last_name' :  { '$ne': 'null' }}, {'last_name'  : value_B  } ]}, 
                                    { '$and' : [ {'mail_adress' :{ '$ne' :'null' }}, {'mail_adress':value_C } ]}
                               ]
                        }
                        )

    readCursor(cursor1)

    # APPROACH 2 ; create a JSON representing the query dynamically
    path_value_dict = {'first_name':value_A, 'last_name':value_B, 'mail_address':value_C} # if embedded it would be for instance name.first_name 
    query= {}
    for key in path_value_dict.keys():
        if path_value_dict[key] != 'null':
            query[key] = path_value_dict[key]


    print query
    cursor2 = db.collection.find({'first_name': 'value_X', 'mail_adress': 'value_Z'})
    readCursor(cursor2) 

推荐答案

使用Mongodb $ or 进行查询.

Use Mongodb $or operator for your query.

$ or运算符对两个或多个<expressions>的数组执行逻辑或运算，并选择满足 <expressions>中至少一个的文档.

The $or operator performs a logical OR operation on an array of two or more <expressions> and selects the documents that satisfy at least one of the <expressions>.

db.collection.find({
     $or : [{ $and : [ {'first_name' : { $ne:null }, {'first_name' :'value_A' }]},
            { $and : [ {'last_name' :  { $ne:null }, {'last_name'  :'value_B' }]}, 
            { $and : [ {'mail_addres' :{ $ne:null }, {'mail_addres':'value_C'}]}
           ]}
    );

这篇关于mongoDB:忽略空字段的多值字段搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！