使用MongoDB获取Percentage Matched属性

Question

我有一个用例，我需要输入一些属性列表，在mongodb中给出搜索结果，它将90%的属性与所有数据匹配。例如:我有这样的数据

[{
    id: 1,
    properties: {
        'property1': 'value1',
        'property2': 'value2',
        'property3': 'value3',
        'property4': 'value4',
        'property5': 'value5'
    }
},
{
    id: 2,
    properties: {
        'property1': 'value1',
        'property2': 'value2',
        'property6': 'value6',
        'property7': 'value7',
        'property8': 'value8',
        'property9': 'value9'
    }
},
{
    id: 3,
    properties: {
        'property9': 'value9'
    }
}]

我想搜索几个属性，我正在寻找至少50%的匹配，其中至少有一个属性应该与两个输入输入查询中的任何一个匹配

find
{
    'property3', 'value3',
    'property7', 'value7'
}

根据上面的输入，它与DB中3个数据中的2个数据相匹配。是否可以使用MongoDB编写查询？

Answer 1

下面是一些应该让您上手的东西(不过，您需要一个更新版本的MongoDB，至少是v3.4.4)：

db.collection.aggregate({
    $addFields: { // we add a helper field
        "propertiesAsArray": { // called "propertiesAsArray"
            $objectToArray: "$properties" // which will be the array representation of the "properties" field
        }
    }
}, {
    $addFields: {
        "matchRatio": { // the value we are looking for is...
            $divide: [ { // ...the ratio between...
                $size: { // ...the number of items...
                    $filter: {
                        "input": "$propertiesAsArray", // ...in our magic property array...
                        "as": "this",
                        "cond": { // ...that match any of our conditions...
                            $or: [
                                { $eq: [ "$$this", { "k": "property3", "v": "value3" } ] },
                                { $eq: [ "$$this", { "k": "property7", "v": "value7" } ] },
                                // ...of which we could have any number...
                            ] 
                        } 
                    }
                }
            }, 2 ] // ...and the number of properties in your query
        }
    }
}, {
    $facet: { // create two separate stages
        "totalNumberOfDocs": [{
            $count: "count" // one just calculates the total number of documents
        }],
        matchingDocs: [{ // and the second stage first filters out the non-matching documents
            $match: {
                "matchRatio": { $gte: 0.50 } // we only want documents with a match ratio >= 50%
            }
        }, {
            $count: "count" // and then counts the remaining ones
        }]
    }
}, {
    $project: { // final calculation of the ratio ("2 out of 3" in your example --> 66%)
        "percentage": {
            $divide: [
                { $arrayElemAt: [ "$matchingDocs.count", 0 ] },
                { $arrayElemAt: [ "$totalNumberOfDocs.count", 0 ] }
            ]
        }
    }
})

如果您只是在寻找匹配的文档，并且不太关心66%的数字，那么这个方法应该可以工作(未经过测试，因为我正在进行测试)。

db.collection.aggregate({
    $addFields: { // we add a helper field
        "propertiesAsArray": { // called "propertiesAsArray"
            $objectToArray: "$properties" // which will be the array representation of the "properties" field
        }
    }
}, {
    $addFields: {
        "matchRatio": { // the value we are looking for is...
            $divide: [ { // ...the ratio between...
                $size: { // ...the number of items...
                    $filter: {
                        "input": "$propertiesAsArray", // ...in our magic property array...
                        "as": "this",
                        "cond": { // ...that match any of our conditions...
                            $or: [
                                { $eq: [ "$$this", { "k": "property3", "v": "value3" } ] },
                                { $eq: [ "$$this", { "k": "property7", "v": "value7" } ] },
                                // ...of which we could have any number...
                            ] 
                        } 
                    }
                }
            }, 2 ] // ...and the number of properties in your query
        }
    }
}, {
    $match: {
        "matchRatio": { $gte: 0.50 } // we only want documents with a match ratio >= 50%
    }
})