Catalog Overview

This filter language isn't directly querying a database. Instead, it is a domain-specific language that was created by our engineers and converted to a database-specific query language. Therefore, only the operations specified in this documentation are validated and permissible. Arithmetic operations, such as +, -, *, and / are not supported.

Data Types

This language supports the following data types:

• INTEGER: Whole number values. For example, 12, -345.
• FLOAT: Floating-point number values. For example, 3.14, -0.89.
• STRING_LITERAL: String values enclosed in single or double quotes. For example, "Hello", 'World'.
• TIMESTAMP: Date time values represented in either RFC3339 format (Example, "2007-12-03T10:15:30.00Z") or the standard Date-Time format (Example, "2006-01-02 15:04:05").
• BOOLEAN: Boolean values represented by the keywords TRUE or FALSE.
• NULL: Keyword which represents a null value.

Formatting Strings

In Bluecore's SQL-like syntax, both single (') and double quotes (") can be used to encapsulate strings. This allows flexibility in how strings containing quotes are represented.

• For example: name='women\'s' or name="women's" are both valid. In this example, if using single quotes around the string, the single quote within the string needs to be escaped (\), but if using double quotes, no escape is necessary for the single quote inside.

Our SQL-like syntax requires that the same type of quote used to begin a string must be escaped if it appears within the string. This is achieved using the backslash (\).

• For instance: name='"women\'s" sweaters' or name="\"women's\" sweaters". In this example, to include quotes of the type that matches the encapsulating quotes, you must escape them. The escaping lets the parser know that these quotes are literals within the string and not terminators of the string.*

Operators

This language supports the following:

Comparison Operators

• =: Equals. For example, age = 30.
• !=: Not equal. For example, age != 30.
• <: Less than. For example, age < 30.
• >: Greater than. For example, age > 30.
• <=: Less than or equal to. For example, age <= 30.
• >=: Greater than or equal to. For example, age >= 30.

Logical Operators

• AND: Both conditions must be true. For example, age >= 18 AND age <= 24.
• OR: At least one of the conditions must be true. For example, color = 'Red' OR color = 'Green'.
• NOT: Inverts the result of the condition. For example, NOT (age < 18).

String Operators

• LIKE: Pattern search. For example, name LIKE '%John%' will match any name that contains 'John'.
• MATCH: Full text search. For example, MATCH(name, 'John') or name MATCH 'John' will match names that contain 'John' as part of any word.
• REGEXP_MATCH: Regular expression matching. This operator matches the entire string against the provided regular expression. For example, REGEXP_MATCH(name, 'J.*e') will match any name that starts with 'J' and ends with 'e'. That is the same as '^J.*e$'.

Other Operators

• IS: Used with NULL to check if a value is null or not. For example, username IS NULL, username IS NOT NULL.
• IN: Used to check if a value falls within a specified list. For example, x IN (1, 2, 3), x NOT IN (1, 2, 3), name IN @nameList.

Functions

• CURRENT_TIMESTAMP or NOW: A function that returns the current timestamp. For example, NOW().
• CURRENT_TIMESTAMP_WITH_OFFSET or NOW_WITH_OFFSET: A function that returns the current timestamp with an offset. A duration string is a possibly signed sequence of decimal numbers, each with optional fraction and a unit suffix, such as "300ms", "-1.5h" or "2h45m". Valid time units are "ns", "us" (or "µs"), "ms", "s", "m", "h", "d", "w", "y". For example, NOW_WITH_OFFSET('-1d').

Operator Precedence

The filter language follows a specific order of precedence for evaluating operators in a query with multiple conditions. The order, from highest to lowest precedence, is as follows:

1. Parantheses ()
2. NOT
3. AND
4. OR

When a query includes multiple operators, parentheses () can be used to override the standard order of precedence and dictate the sequence in which expressions should be evaluated.

Variables

Variables expressed in this syntax are akin to prepared statements. Written with an @ prefix, variables are placeholders for actual values to be inserted into the query dynamically while it is being executed.

Here are some examples of writing a query with and without variables.

Query without variables:

Let's say you want to find all products in the Electronics category with a price greater than $500. The filter clause in this case would look something like this:

ParameterizedFilter { filter = "product_category = 'Electronics' AND price > 500" }

Query with variables:

Now, let's say you want to make the above code more dynamic, such that the product category and price threshold can be varied. Here, we would substitute 'Electronics' and 500 with variables like this:

ParameterizedFilter { filter = "product_category = @category AND price > @priceThreshold" parameters = { category = "Electronics" priceThreshold = 500 } }

In this example, @category is a placeholder for the product category and @priceThreshold is a placeholder for the price cut-off. The actual values for these variables should be provided at the time of executing the query.

Examples:

1. Equality & Comparison Operations:

• Find 'Electronics' category products priced over 500: product_category = 'Electronics' AND price > 500

2. Logical Operations with Combined Conditions:

• Find 'Electronics' category products that are priced over 500 and has stock over 100 units: (product_category = 'Electronics' AND price > 500) AND stock_quantity > 100

3. Mixed Operators:

• Find products that are either in the 'Electronics' or 'Books' category, priced over 100 and were updated before a particular timestamp: (product_category = 'Electronics' OR product_category = 'Books') AND price > 100 AND updated_at < "2021-01-01T00:00:00Z"

4. String & Logical Operations:

• Find 'Electronics' products whose name starts with 'Apple' and priced over 500: product_category = 'Electronics' AND product_name LIKE 'Apple%' AND price > 500

5. Combined NULL checks, String and Logical operations:

• Find 'Grocery' products that are not yet updated (missing updated_at) and start with 'Organic': product_category = 'Grocery' AND updated_at IS NULL AND product_name LIKE 'Organic%'

6. Using IN Checks with other operators:

• Finding products with specific ids that are in stock: product_id IN (123, 456, 789) AND stock_quantity > 0

7. Complex Timestamp operation:

• Finding 'Electronics' products, created after a certain timestamp and priced under 200: product_category = 'Electronics' AND created_at > "2021-01-01T00:00:00Z" AND price < 200