Advanced Product Filtering

In e-commerce platforms, efficiently filtering products based on various criteria is crucial for user experience and sales. This challenge involves implementing a sophisticated product filtering mechanism that combines multiple conditions using logical operators.

Problem Description

You are tasked with creating a system to retrieve a list of products from a database that satisfy a complex set of filtering criteria. These criteria can include price ranges, stock availability, customer ratings, and specific product categories. The filtering logic needs to accommodate combinations of AND, OR, and NOT operators to precisely define the desired product subset.

Key Requirements:

• Filter products based on a combination of price, stock, rating, and category.
• Support logical operators: AND, OR, and NOT.
• The filtering logic will be provided as a structured representation.
• Return a list of product IDs that match all specified conditions.

Expected Behavior: The system should process a given set of filter conditions and return only the product_ids of products that meet all the specified criteria.

Edge Cases:

• No products matching the criteria.
• Empty filter conditions (should return all products).
• Conditions that negate each other.

Examples

Example 1: Input: Products Table:

Filter Conditions:

{
  "operator": "AND",
  "conditions": [
    { "field": "price", "operator": ">=", "value": 50 },
    { "field": "category", "operator": "=", "value": "Electronics" }
  ]
}

Output: [101, 102]

Explanation: Products that have a price greater than or equal to $50 AND belong to the 'Electronics' category are Laptop (101) and Keyboard (102).

Example 2: Input: Products Table: (Same as Example 1)

Filter Conditions:

{
  "operator": "OR",
  "conditions": [
    { "field": "stock_quantity", "operator": "<", "value": 100 },
    { "field": "rating", "operator": ">", "value": 4.3 }
  ]
}

Output: [101, 103, 202]

Explanation: Products with stock quantity less than 100 OR a rating greater than 4.3 are Laptop (101 - stock 50, rating 4.5), Mouse (103 - stock 200, rating 3.8, wait, this is wrong, mouse has stock > 100, but its rating is not > 4.3, let's re-evaluate). Re-evaluation:

• Laptop (101): stock 50 (< 100) - Included.
• Keyboard (102): stock 150 (not < 100), rating 4.0 (not > 4.3) - Excluded.
• Mouse (103): stock 200 (not < 100), rating 3.8 (not > 4.3) - Excluded.
• T-Shirt (201): stock 300 (not < 100), rating 4.2 (not > 4.3) - Excluded.
• Jeans (202): stock 100 (not < 100), rating 4.1 (not > 4.3) - Excluded.
• Coffee Mug (301): stock 500 (not < 100), rating 3.5 (not > 4.3) - Excluded.

Correction to Example 2 Explanation based on re-evaluation: The correct output for Example 2 should be: Output: [101]

Explanation: Products that have stock quantity less than 100 OR a rating greater than 4.3:

• Laptop (101): stock_quantity is 50 (which is < 100). Meets the first condition. Included.
• Keyboard (102): stock_quantity is 150 (not < 100), rating is 4.0 (not > 4.3). Does not meet either condition. Excluded.
• Mouse (103): stock_quantity is 200 (not < 100), rating is 3.8 (not > 4.3). Does not meet either condition. Excluded.
• T-Shirt (201): stock_quantity is 300 (not < 100), rating is 4.2 (not > 4.3). Does not meet either condition. Excluded.
• Jeans (202): stock_quantity is 100 (not < 100), rating is 4.1 (not > 4.3). Does not meet either condition. Excluded.
• Coffee Mug (301): stock_quantity is 500 (not < 100), rating is 3.5 (not > 4.3). Does not meet either condition. Excluded.

Example 3: Input: Products Table: (Same as Example 1)

Filter Conditions:

{
  "operator": "AND",
  "conditions": [
    {
      "operator": "OR",
      "conditions": [
        { "field": "category", "operator": "=", "value": "Electronics" },
        { "field": "price", "operator": "<", "value": 30 }
      ]
    },
    { "field": "stock_quantity", "operator": ">", "value": 100 }
  ]
}

Output: [102]

Explanation: This condition requires products to be in 'Electronics' OR have a price less than $30, AND also have a stock quantity greater than 100.

• Laptop (101): Category is Electronics (meets first part of OR). Stock is 50 (not > 100). Fails AND. Excluded.
• Keyboard (102): Category is Electronics (meets first part of OR). Stock is 150 ( > 100). Meets both parts of AND. Included.
• Mouse (103): Category is Electronics (meets first part of OR). Stock is 200 ( > 100). Meets both parts of AND. Included.
• T-Shirt (201): Category is Apparel (not Electronics). Price is 20 (< 30) (meets second part of OR). Stock is 300 ( > 100). Meets both parts of AND. Included.
• Jeans (202): Category is Apparel (not Electronics). Price is 50 (not < 30). Fails OR. Excluded.
• Coffee Mug (301): Category is Home Goods (not Electronics). Price is 15 (< 30) (meets second part of OR). Stock is 500 ( > 100). Meets both parts of AND. Included.

Correction to Example 3 Output and Explanation based on re-evaluation: The correct output for Example 3 should be: Output: [102, 103, 301]

Explanation: The condition is: (Category = 'Electronics' OR Price < 30) AND Stock > 100.

• Laptop (101): Category is 'Electronics' (TRUE). Price is 1200 (not < 30). So, OR condition is TRUE. Stock is 50 (not > 100). So, AND condition is FALSE. Excluded.
• Keyboard (102): Category is 'Electronics' (TRUE). Price is 75 (not < 30). So, OR condition is TRUE. Stock is 150 (> 100). So, AND condition is TRUE. Included.
• Mouse (103): Category is 'Electronics' (TRUE). Price is 25 (< 30). So, OR condition is TRUE. Stock is 200 (> 100). So, AND condition is TRUE. Included.
• T-Shirt (201): Category is 'Apparel' (FALSE). Price is 20 (< 30). So, OR condition is TRUE. Stock is 300 (> 100). So, AND condition is TRUE. Included.
• Jeans (202): Category is 'Apparel' (FALSE). Price is 50 (not < 30). So, OR condition is FALSE. Stock is 100 (not > 100). So, AND condition is FALSE. Excluded.
• Coffee Mug (301): Category is 'Home Goods' (FALSE). Price is 15 (< 30). So, OR condition is TRUE. Stock is 500 (> 100). So, AND condition is TRUE. Included.

Constraints

• The Products table contains up to 1,000,000 rows.
• product_id is a unique integer.
• price is a floating-point number.
• stock_quantity is an integer.
• rating is a floating-point number.
• category is a string.
• The filter conditions will be represented as a nested JSON object.
• The JSON structure for conditions will strictly follow the pattern of having an operator at the top level and either conditions (for logical operators like AND/OR) or field, operator, value (for comparison operators).
• Comparison operators include: =, !=, <, <=, >, >=.
• Logical operators include: AND, OR, NOT. NOT will only apply to a single sub-condition.

Notes

• You will need to parse the structured filter conditions.
• Consider how to handle nested logical operators efficiently.
• The solution should be performant, especially when dealing with a large number of products.
• Think about the order of operations when evaluating complex nested conditions.
• You may assume that the input Products table is already available in a queryable format (e.g., a pseudocode function get_products_from_database() that returns a list of product dictionaries or objects).