如何将Excel的AND、OR嵌套条件转换为MongoDB查询条件

Great question! Converting these nested logical conditions from your Excel-style format to MongoDB's query language is totally doable—here's a step-by-step approach plus implementation logic you can follow:

1. Map Basic Operators First #

Start by creating a direct mapping between your Excel-style operators and MongoDB's query operators. This is the foundation of the conversion:

• Equality/inequality:
  • Excel = → MongoDB $eq
  • Excel <>, != → MongoDB $ne
• Comparison:
  • Excel < → MongoDB $lt
  • Excel > → MongoDB $gt
  • Excel <= → MongoDB $lte
  • Excel >= → MongoDB $gte
• Logical grouping:
  • Excel AND(...) → MongoDB $and
  • Excel OR(...) → MongoDB $or

2. Use Recursion to Handle Nested Structures #

Since your conditions support arbitrary nesting (e.g., AND inside OR inside AND), recursion is the perfect tool here. The core idea is:

• If the current condition is a logical group (AND/OR), parse its inner arguments and convert each one recursively
• If it's a simple key-value condition, convert it directly using the operator map above

Key Parsing Step: Handle Nested Parentheses #

When splitting the inner arguments of an AND/OR function, you can't just split on commas—you need to track open/closed parentheses to avoid splitting inside nested groups. For example, in OR(AND(a,b),c), you want to split into AND(a,b) and c, not AND(a, b), c.

3. Pseudocode for Conversion #

Here's a simplified pseudocode example to illustrate the logic:

function convertExcelToMongo(conditionString):
    // Trim whitespace and remove outer parentheses if needed
    cleaned = conditionString.trim().replace(/^AND\(|^OR\(|\)$/g, "")
    
    if conditionString starts with "AND(" or "OR(":
        // Determine the MongoDB logical operator
        mongoLogicalOp = "$and" if conditionString.startsWith("AND(") else "$or"
        // Split inner arguments safely (respect nested parentheses)
        innerConditions = splitSafe(cleaned, ",")
        // Recursively convert each inner condition
        convertedInner = [convertExcelToMongo(arg.trim()) for arg in innerConditions]
        return { mongoLogicalOp: convertedInner }
    else:
        // Parse simple condition (e.g., "name <> 'bob'")
        key, excelOp, value = parseSimpleCondition(conditionString)
        // Map Excel operator to MongoDB
        mongoOp = operatorMap[excelOp]
        // Clean value (remove quotes, convert numbers if needed)
        cleanValue = value.replace(/['"]/g, "")
        // Check if value is a number and convert if so
        if cleanValue.match(/^\d+(\.\d+)?$/):
            cleanValue = parseFloat(cleanValue)
        return { key: { mongoOp: cleanValue } }

// Helper function to split string on commas, ignoring commas inside parentheses
function splitSafe(str, delimiter):
    result = []
    current = ""
    parenthesisCount = 0
    for char in str:
        if char == delimiter and parenthesisCount == 0:
            result.push(current)
            current = ""
        else:
            if char == "(":
                parenthesisCount +=1
            elif char == ")":
                parenthesisCount -=1
            current += char
    result.push(current)
    return result

4. Test with Your Examples #

Let's verify the logic against your provided examples:

Example 1 #

Excel Condition:
OR(AND(name <> 'bob', age < 10),AND(name <> 'john', age > 40), age = 40)
Converted MongoDB Query:

{
  $or: [
    { $and: [{ name: { $ne: 'bob' } }, { age: { $lt: 10 } }] },
    { $and: [{ name: { $ne: 'john' } }, { age: { $gt: 40 } }] },
    { age: { $eq: 40 } }
  ]
}

Example 2 #

Excel Condition:
AND(OR(name <> 'bob', age < 10),AND(name <> 'john', age > 40), age = 40)
Converted MongoDB Query:

{
  $and: [
    { $or: [{ name: { $ne: 'bob' } }, { age: { $lt: 10 } }] },
    { $and: [{ name: { $ne: 'john' } }, { age: { $gt: 40 } }] },
    { age: { $eq: 40 } }
  ]
}

Both match your expected outputs perfectly!

5. Edge Cases to Consider #

• Unquoted numeric values: Make sure your parser converts strings like age < 10 to a number 10 instead of a string "10" (MongoDB treats numbers and strings differently in queries)
• Extra whitespace: Your parser should ignore spaces around operators, commas, and parentheses (e.g., AND( name <> 'bob' , age < 10 ) should work the same as the compact version)
• Nested depth: Recursion will handle any level of nesting, so you don't need to worry about deeply nested conditions

内容的提问来源于stack exchange，提问作者No one