External Filtering Examples

Basic Equals Operation

Checking if a user ID equals 1:

{
  "expression": [{
    "statement": {
      "left": {
        "tag": "col",
        "operand": "users.id"
      },
      "op": "=",
      "right": {
        "operand": "1"
      }
    }
  }]
}

Between Operation

Finding transactions with amount between 100 and 1000:

{
  "expression": [{
    "statement": {
      "left": {
        "tag": "col",
        "operand": "transactions.amount"
      },
      "op": "between",
      "right": {
        "operand": ["100", "1000"]
      }
    }
  }]
}

Contains Operation

Finding users with email containing '@company.com':

{
  "expression": [{
    "statement": {
      "left": {
        "tag": "col",
        "operand": "users.email"
      },
      "op": "contains",
      "right": {
        "operand": "@company.com"
      }
    }
  }]
}

Multiple Conditions Example

Finding active premium users who have made at least 5 purchases:

{
  "expression": [
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.status"
        },
        "op": "=",
        "right": {
          "operand": "active"
        }
      }
    },
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.account_type"
        },
        "op": "=",
        "right": {
          "operand": "premium"
        }
      }
    },
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.purchase_count"
        },
        "op": ">=",
        "right": {
          "operand": "5"
        }
      }
    }
  ]
}

Case-Insensitive Pattern Matching (ilike)

Finding products with names starting with 'phone', regardless of case:

{
  "expression": [{
    "statement": {
      "left": {
        "tag": "col",
        "operand": "products.name"
      },
      "op": "ilike",
      "right": {
        "operand": "phone%"
      }
    }
  }]
}

Array Membership (in)

Finding orders with specific status values:

{
  "expression": [{
    "statement": {
      "left": {
        "tag": "col",
        "operand": "orders.status"
      },
      "op": "in",
      "right": {
        "operand": ["pending", "processing", "shipped"]
      }
    }
  }]
}

Complex Multiple Conditions

Finding high-value transactions (>1000) made in the last 30 days by premium users:

{
  "expression": [
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "transactions.amount"
        },
        "op": ">",
        "right": {
          "operand": "1000"
        }
      }
    },
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "transactions.date"
        },
        "op": ">=",
        "right": {
          "operand": "2024-12-29"
        }
      }
    },
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.account_type"
        },
        "op": "=",
        "right": {
          "operand": "premium"
        }
      }
    }
  ]
}

Using And/Or

By default, all statements will be considered an 'and' statement, and nothing needs to be specified. You'll only need to specify whether or is true when you want to use it.

For readability purposes, however, you can specify or is false if you'd like.

The two examples below demonstrate this and would return the same result.

{
  "expression": [
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.id"
        },
        "op": "=",
        "right": {
          "operand": "1"
        }
      }
    }
  ]
}

// Verbose specification of "or"

{
  "expression": [
    {
      "or": false,
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.id"
        },
        "op": "=",
        "right": {
          "operand": "1"
        }
      }
    }
  ]
}

Two Conditions Combined with OR

This example filters for users whose status is 'inactive' OR whose account type is 'basic'.

{
  "expression": [
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.status"
        },
        "op": "=",
        "right": {
          "operand": "inactive"
        }
      }
    },
    {
      "or": true,
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.account_type"
        },
        "op": "=",
        "right": {
          "operand": "basic"
        }
      }
    }
  ]
}

Three Conditions with AND and OR

This example filters for active users AND (whose purchase count is less than 10 OR whose last login is before a specific date).

{
  "expression": [
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.status"
        },
        "op": "=",
        "right": {
          "operand": "active"
        }
      }
    },
    {
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.purchase_count"
        },
        "op": "<",
        "right": {
          "operand": "10"
        }
      }
    },
    {
      "or": true,
      "statement": {
        "left": {
          "tag": "col",
          "operand": "users.last_login"
        },
        "op": "<",
        "right": {
          "operand": "2024-01-01"
        }
      }
    }
  ]
}

Using And/Or Groups - (Condition A AND Condition B) OR (Condition C AND Condition D)

Here's how the logic (a = 1 AND b = 2) OR (a = 4 AND b = 5) would be represented:

{
    "expression": [
      {
        "or": false,
        "type": "group",
        "group": {
          "expression": [
            {
              "or": false,
              "statement": {
                "left": { "operand": "your_table.a" },
                "op": "=",
                "right": { "operand": "1" }
              },
              "type": "statement"
            },
            {
              "or": false,
              "statement": {
                "left": { "operand": "your_table.b" },
                "op": "=",
                "right": { "operand": "2" }
              },
              "type": "statement"
            }
          ]
        }
      },
      {
        "or": true,
        "type": "group",
        "group": {
          "expression": [
            {
              "or": false,
              "statement": {
                "left": { "operand": "your_table.a" },
                "op": "=",
                "right": { "operand": "4" }
              },
              "type": "statement"
            },
            {
              "or": false,
              "statement": {
                "left": { "operand": "your_table.b" },
                "op": "=",
                "right": { "operand": "5" }
              },
              "type": "statement"
            }
          ]
        }
      }
    ]
  }