Articles // dev

Filtering data via SQL-like query

Sergey Grachev
published: ⏰ 14
DSL
Query Filter

Some time ago there was an article about how to implement a formula interpreter in Formula Interpreter (DSL). When implementing tekBlueprint.frame, there was a need to make a universal query language for the REST API to obtain tabular data for the UI. Actually this is what we will talk about.

Introduction

As mentioned above, there was a need to request data from the server layer using a certain universal format for describing requests. Of course, we can say that everything has already been invented before us and there are such things as GraphQL, OData, which completely solve this problem. In general, they can and should be used, but there were some reasons to make your own version of the queries.

Task

First, I will explain the reasons for implementing my solution:

  1. I didn’t want to add additional libraries to the project that would only require a small percentage of the functionality. only data filtering is required:
  2. There is no need to define a list of fields to select - they are specified explicitly and everything that is not included in the list is prohibited.
  3. Mechanisms for changing data are not required (for example, GraphQL Mutations) - everything is done according to the principles of the RESTful API.
  4. Need full control over the syntax with the ability to expand.

If we discard ready-made solutions, we get several options for solving the problem:

  1. The oldest and most famous since ancient technologies - we provide everything in advance in the controller parameters and build a request according to the parameters specified in the UI.
  2. We generate JSON with a filter on the UI side and pass it in the request - a more flexible option, but with certain limitations.
  3. We use an SQL-like query, parse it and convert it to SQL for the DBMS - usually it immediately discourages us, since we need to write a parser. You can take something ready-made. For example, Apache Calcite, but it looks like a strong overcomplication for solving a similar problem.
Through request parametersFilter as JSONFilter as a text query
ProsSimple and clear to implementGreater flexibilityCompact and human-readable view
Highest query parsing speedEase of generation in JavaScript -> JSONAbility to write UI queries manually
High speed of query parsing
ConsQuite cumbersome queriesNo less cumbersome queriesYou need to implement your own parser
It is necessary to provide all options in advanceYou cannot pass it in the body of a GET request, only in parameters or use POSTSlower query parsing speed compared to others
It is possible, but it is difficult to make a generalized solutionOnly machine query generation
Only machine query generation

As a result, I settled on options 2 and 3. The second option is by and large used as the base model for the request ( hereinafter referred to as the Query class), into which the third option with the request is given.

Filter as JSON

To help you understand, I’ll give you an example of what I’m talking about. Below is an example of a user request generated in code (Java).

Java 
var query = Query.builder().page(10).size(20)
        .filter(or(
                isNotNull("_name"),
                and(eq("_name", "maria"), ilike("_name", "ari"), eq("type", "local")),
                and(eq("_name", "smith"), eq("type", "oidc"))))
        .search("any text")
        .build();

Which is serialized as follows:

JSON 
{
  "filter": {
    "or": [
      { "field": "_name", "op": "ISNN" },
      {
        "and": [
          { "field": "_name", "op": "EQ", "value": "maria" },
          { "field": "_name", "op": "ILIKE", "value": "ari" },
          { "field": "type", "op": "EQ", "value": "local" }
        ]
      },
      {
        "and": [
          { "field": "_name", "op": "EQ", "value": "smith" },
          { "field": "type", "op": "EQ", "value": "oidc" }
        ]
      }
    ]
  },
  "search": "any text",
  "page": 10,
  "size": 20
}

Passing such JSON in a data request is not very convenient:

  • It is necessary to somehow serialize it into request parameters. The length of the URL has a limit, although it is quite large in some browsers.
  • An alternative is to use POST instead of GET. But this breaks the rules for using the RESTful API – the data is not changed, but only read.

SQL-like query

The same Query class is used here, but the filter parameter is obtained from the query text.

Java 
var query = Query.builder().page(10).size(20)
        .filter(QueryFilterCompiler.compile("""
                _name is not null
                        or (_name = "maria" or _name ilike "%ari%" and type="local")
                        or (_name = "smith" and type="type")
                """))
        .search("any text")
        .build();

A request in this form can already be passed in URL parameters, masking special characters.

ANTLR4

We do it by analogy with Formula Interpreter (DSL).

Connecting the plugin
XML 
<build>
    <plugins>
        <plugin>
            <groupId>org.antlr</groupId>
            <artifactId>antlr4-maven-plugin</artifactId>
            <version>4.13.2</version>
            <executions>
                <execution>
                    <id>antlr</id>
                    <goals>
                        <goal>antlr4</goal>
                    </goals>
                </execution>
            </executions>
            <configuration>
                <visitor>true</visitor>
            </configuration>
        </plugin>
    </plugins>
</build>

The file with the grammars itself should be located in the src/main/antlr4/com/github/devmix/sample/filter/antlr4/QueryFilter.g4 directory, and the final package for the classes will be com.github.devmix.sample.filter.antlr4.

Grammar and parser generation

Unlike the formula, the grammar for the filter will be a little more complex. Example grammar (QueryFilter.g4) below:

Antlrv4 
grammar QueryFilter;

filter: expr EOF;

expr      : left=expr op=(AND | OR) right=expr      # logicalExpr
          | LPAREN expr RPAREN                      # parenExpr
          | operand                                 # operandExpr
          ;

operand   : field op=(EQ | NE | LT | LTE | GT | GTE) value           # compareExpr
          | field op=(IS_NULL | IS_NOT_NULL)                         # nullCheckExpr
          | field op=(LIKE | ILIKE) STRING                           # stringPatternExpr
          | field op=(IN | NOT_IN) LPAREN valueList RPAREN           # inExpr
          ;

valueList : value (',' value)*;

value     : type=(STRING | INTEGER | FLOAT | BOOLEAN | NULL)
          ;

field     : IDENTIFIER ('.' IDENTIFIER)* // Support nested fields (e.g., user.name)
          ;

// LOGICAL OPERATORS

AND         : 'and' | 'AND';
OR          : 'or' | 'OR';

// COMPARE OPERATORS

EQ          : 'eq' | '=' | '==';
NE          : 'ne' | '!=' | '<>';
LT          : 'lt' | '<';
LTE         : 'lte' | '<=';
GT          : 'gt' | '>';
GTE         : 'gte' | '>=';
IS_NULL     : 'is' (' '? 'null');
IS_NOT_NULL : 'is' (' '? 'not') (' '? 'null');
LIKE        : 'like';       // field ilike "%asd%"
ILIKE       : 'ilike';      // field like "%asd%"
IN          : 'in';
NOT_IN      : 'not' (' '? 'in');

// ATOMS

STRING      : DQUOTE (~["\\\r\n] | '\\' ["\\])* DQUOTE
            | SQUOTE (~['\\\r\n] | '\\' ['\\])* SQUOTE
            ;
INTEGER     : [+-]? [0-9]+
            ;
FLOAT       : [+-]? [0-9]+ '.' [0-9]+
            ;
BOOLEAN     : 'true'
            | 'false'
            ;
NULL        : 'null'
            ;
IDENTIFIER  : [a-zA-Z_] [a-zA-Z_0-9]*;

// GROUPING CHARS

LPAREN      : '('
            ;
RPAREN      : ')'
            ;
SQUOTE      : '\''
            ;
DQUOTE      : '"'
            ;

// WHITE SPACES & COMMENTS

WS          : [ \r\n\t]+ -> skip
            ;
COMMENT     : '/*' .*? '*/' -> skip
            ;
LINE_COMMENT: '//' ~[\r\n]* -> skip
            ;

Here you can see that multiple representations can be used for comparison operations. For example, to compare equivalence you can use eq, =, ==. To whom it is more convenient and familiar.

There are also logical operations or, and.

After describing the grammar, you can run the Maven command to generate classes: mvn antlr4:antlr4. As a result, we get the following classes, which will be required further:

  • QueryFilterLexer.java - lexical for parsing the formula into tokens.
  • QueryFilterParser.java – a parser for building a formula parsing tree (AST).
  • QueryFilterBaseVisitor.java is a base visitor class that can be used to traverse the parse tree. To create this class, you must enable <visitor>true</visitor> in the plugin.

Abstract Syntax Tree

Now you can create a visitor class that will build a FilterRule based on the request. Again, to simplify it, I combined it with the compiler class and added a compile method, which takes a filter string and returns the root AST node (FilterRule).

Java 
/**
 * This class is responsible for compiling a filter string into an AST (Abstract Syntax Tree)
 * using the ANTLR4-generated parser and lexer.
 *
 * @author Sergey Grachev
 */
public final class QueryFilterCompiler extends QueryFilterBaseVisitor<FilterRule> {

    /**
     * Compiles a filter string into a FilterRule object.
     *
     * @param filter The filter string to compile.
     * @return A FilterRule object representing the parsed filter, or null if syntax errors are present.
     */
    @Nullable
    public static FilterRule compile(final String filter) {
        final var tokens = new CommonTokenStream(new QueryFilterLexer(CharStreams.fromString(filter)));
        final var parser = new QueryFilterParser(tokens);
        final var tree = parser.filter();
        if (parser.getNumberOfSyntaxErrors() > 0) {
            System.err.println("Syntax errors detected!");
            return null;
        }
        return new QueryFilterCompiler().visit(tree);
    }

    /**
     * Visits a filter context and returns the corresponding FilterRule.
     *
     * @param ctx The filter context to visit.
     * @return A FilterRule representing the parsed filter.
     */
    @Override
    public FilterRule visitFilter(final QueryFilterParser.FilterContext ctx) {
        return visit(ctx.expr());
    }

    /**
     * Visits a logical expression context and returns the corresponding FilterRule.
     *
     * @param ctx The logical expression context to visit.
     * @return A FilterRule representing the parsed logical expression.
     */
    @Override
    public FilterRule visitLogicalExpr(final QueryFilterParser.LogicalExprContext ctx) {
        final var left = visit(ctx.left);
        final var right = visit(ctx.right);
        final var operator = logicalOperatorOf(ctx.op);

        // flatten same-operator groups for cleaner AST

        final var rules = new ArrayList<FilterRule>();
        if (left instanceof final FilterLogicalRule l && l.getOperator().equals(operator)) {
            rules.addAll(l.getFilters());
        } else {
            rules.add(left);
        }

        if (right instanceof final FilterLogicalRule r && r.getOperator().equals(operator)) {
            rules.addAll(r.getFilters());
        } else {
            rules.add(right);
        }

        return new FilterLogicalRule(operator, rules);
    }

    /**
     * Visits a parenthesized expression context and returns the corresponding FilterRule.
     *
     * @param ctx The parenthesized expression context to visit.
     * @return A FilterRule representing the parsed parenthesized expression.
     */
    @Override
    public FilterRule visitParenExpr(final QueryFilterParser.ParenExprContext ctx) {
        return visit(ctx.expr());
    }

    /**
     * Visits a comparison expression context and returns the corresponding FilterRule.
     *
     * @param ctx The comparison expression context to visit.
     * @return A FilterRule representing the parsed comparison expression.
     */
    @Override
    public FilterRule visitCompareExpr(final QueryFilterParser.CompareExprContext ctx) {
        final var field = ctx.field().getText();
        final var operator = compareOperatorOf(ctx.op);
        final var value = parseValue(ctx.value().getText(), ctx.value().type);
        return new FilterFieldRule(field, operator, value);
    }

    /**
     * Visits a null check expression context and returns the corresponding FilterRule.
     *
     * @param ctx The null check expression context to visit.
     * @return A FilterRule representing the parsed null check expression.
     */
    @Override
    public FilterRule visitNullCheckExpr(final QueryFilterParser.NullCheckExprContext ctx) {
        final var field = ctx.field().getText();
        final var operator = compareOperatorOf(ctx.op);
        return new FilterFieldRule(field, operator);
    }

    /**
     * Visits a string pattern expression context and returns the corresponding FilterRule.
     *
     * @param ctx The string pattern expression context to visit.
     * @return A FilterRule representing the parsed string pattern expression.
     */
    @Override
    public FilterRule visitStringPatternExpr(final QueryFilterParser.StringPatternExprContext ctx) {
        final var field = ctx.field().getText();
        final var operator = compareOperatorOf(ctx.op);
        final var string = ctx.STRING();
        final var value = parseValue(string.getText(), string.getSymbol());
        return new FilterFieldRule(field, operator, value);
    }

    /**
     * Visits an "in" expression context and returns the corresponding FilterRule.
     *
     * @param ctx The "in" expression context to visit.
     * @return A FilterRule representing the parsed "in" expression.
     */
    @Override
    public FilterRule visitInExpr(final QueryFilterParser.InExprContext ctx) {
        final var field = ctx.field().getText();
        final var operator = compareOperatorOf(ctx.op);

        final var values = new ArrayList<Object>();
        final var valuesParser = ctx.valueList().value();
        for (final var valueParser : valuesParser) {
            values.add(parseValue(valueParser.getText(), valueParser.type));
        }

        return new FilterFieldRule(field, operator, values);
    }

    /**
     * Parses a value from its textual representation and token type.
     *
     * @param text  The text of the value to parse.
     * @param token The token type of the value.
     * @return The parsed value object.
     */
    @Nullable
    private Object parseValue(final @Nullable String text, final Token token) {
        final var type = token.getType();
        final Object value;
        if (text == null || type == QueryFilterParser.NULL) {
            value = null;
        } else if (type == QueryFilterParser.STRING) {
            value = text.substring(1, text.length() - 1);
        } else if (type == QueryFilterParser.BOOLEAN) {
            value = Boolean.valueOf(text);
        } else if (type == QueryFilterParser.FLOAT) {
            value = Double.valueOf(text);
        } else if (type == QueryFilterParser.INTEGER) {
            value = Long.valueOf(text);
        } else {
            value = null;
        }
        return value;
    }

    /**
     * Converts a token representing a logical operator into its corresponding FilterLogicalRule.Operator enum.
     *
     * @param op The token representing the logical operator.
     * @return The corresponding FilterLogicalRule.Operator enum.
     */
    private FilterLogicalRule.Operator logicalOperatorOf(final Token op) {
        final var type = op.getType();
        if (type == QueryFilterParser.AND) {
            return FilterLogicalRule.Operator.AND;
        }
        if (type == QueryFilterParser.OR) {
            return FilterLogicalRule.Operator.OR;
        }
        throw new UnsupportedOperationException("Logical operator '" + op.getText() + "' not implemented");
    }

    /**
     * Converts a token representing a comparison operator into its corresponding FilterFieldRule.Operator enum.
     *
     * @param op The token representing the comparison operator.
     * @return The corresponding FilterFieldRule.Operator enum.
     */
    private FilterFieldRule.Operator compareOperatorOf(final Token op) {
        return switch (op.getType()) {
            case QueryFilterParser.EQ -> FilterFieldRule.Operator.EQ;
            case QueryFilterParser.NE -> FilterFieldRule.Operator.NE;
            case QueryFilterParser.LIKE -> FilterFieldRule.Operator.LIKE;
            case QueryFilterParser.ILIKE -> FilterFieldRule.Operator.ILIKE;
            case QueryFilterParser.GT -> FilterFieldRule.Operator.GT;
            case QueryFilterParser.GTE -> FilterFieldRule.Operator.GTE;
            case QueryFilterParser.LT -> FilterFieldRule.Operator.LT;
            case QueryFilterParser.LTE -> FilterFieldRule.Operator.LTE;
            case QueryFilterParser.IN -> FilterFieldRule.Operator.IN;
            case QueryFilterParser.NOT_IN -> FilterFieldRule.Operator.NIN;
            case QueryFilterParser.IS_NULL -> FilterFieldRule.Operator.ISN;
            case QueryFilterParser.IS_NOT_NULL -> FilterFieldRule.Operator.ISNN;
            default ->
                    throw new UnsupportedOperationException("Compare operator '" + op.getText() + "' not implemented");
        };
    }
}

Each method creates a new rule for the request. This is either a simple operation on a field or a logical group of operations on fields.

Below are the classes for generating Query and filtering rules.

Java 
/**
 * Represents a query for filtering and searching data.
 *
 * @author Sergey Grachev
 */
@Getter
@Setter
@Builder
@JsonInclude(JsonInclude.Include.NON_NULL)
@NoArgsConstructor
@AllArgsConstructor
public final class Query implements Serializable {

    @Serial
    private static final long serialVersionUID = -7649292546603153860L;

    /**
     * The filter rule used to define the filtering criteria for the query.
     */
    @JsonDeserialize(using = FilterDeserializer.class)
    @JsonSerialize(using = FilterSerializer.class)
    @JsonProperty("filter")
    private @Nullable FilterRule filter;

    /**
     * The search term used to define the global search criteria for the query.
     */
    @JsonProperty("search")
    private @Nullable String search;

    /**
     * The page number for pagination of results.
     */
    @JsonProperty("page")
    private int page = 0;

    /**
     * The size of the result set to return.
     */
    @JsonProperty("size")
    private int size = 20;
}
Java 
/**
 * Represents a filter rule for a specific field.
 *
 * @author Sergey Grachev
 */
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
public final class FilterFieldRule implements FilterRule {

    /**
     * The field name to which this rule applies.
     */
    @JsonProperty("field")
    private String field;

    /**
     * The operator used for comparison in this filter rule.
     */
    @JsonProperty("op")
    private Operator operator;

    /**
     * The value against which the field is compared.
     */
    @JsonProperty("value")
    private @Nullable Object value;

    public FilterFieldRule(final String field, final Operator operator) {
        this.field = field;
        this.operator = operator;
    }

    public static FilterFieldRule eq(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.EQ, value);
    }

    public static FilterFieldRule ne(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.NE, value);
    }

    public static FilterFieldRule ilike(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.ILIKE, value);
    }

    public static FilterFieldRule gt(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.GT, value);
    }

    public static FilterFieldRule lt(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.LT, value);
    }

    public static FilterFieldRule gte(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.GTE, value);
    }

    public static FilterFieldRule lte(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.LTE, value);
    }

    public static FilterFieldRule in(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.IN, value);
    }

    public static FilterFieldRule nin(final String field, final Object value) {
        return new FilterFieldRule(field, Operator.NIN, value);
    }

    public static FilterFieldRule isNull(final String field) {
        return new FilterFieldRule(field, Operator.ISN);
    }

    public static FilterFieldRule isNotNull(final String field) {
        return new FilterFieldRule(field, Operator.ISNN);
    }

    /**
     * Enum representing the various operators used in filter rules.
     * Each operator defines a specific type of comparison or condition
     * that can be applied to a field.
     */
    public enum Operator {
        EQ, NE, LIKE, ILIKE, GT, LT, GTE, LTE, IN, NIN, ISN, ISNN
    }
}
Java 
/**
 * Represents a logical rule for filtering, which can be either AND or OR.
 *
 * @author Sergey Grachev
 */
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public final class FilterLogicalRule implements FilterRule {

    /**
     * The operator for this logical rule, indicating whether it's an AND or OR operation.
     */
    @JsonProperty("op")
    private Operator operator;

    /**
     * A list of filter rules that are combined under this logical rule.
     */
    @Singular
    @JsonProperty("f")
    private List<FilterRule> filters;

    public FilterLogicalRule(final List<FilterRule> filters) {
        this.filters = filters;
    }

    /**
     * Enum representing the logical operators for filter rules.
     */
    public enum Operator {
        AND, OR
    }

    public static FilterLogicalRule or(final FilterRule... rules) {
        return new FilterLogicalRule(Operator.OR, List.of(rules));
    }

    public static FilterLogicalRule and(final FilterRule... rules) {
        return new FilterLogicalRule(Operator.AND, List.of(rules));
    }
}
Java 
/**
 * Represents a filter rule that can be applied to query data.
 *
 * @author Sergey Grachev
 */
public interface FilterRule {
}

Here:

  • FilterRule – a marker interface for all comparison operations in the filter condition.
  • Query – class for describing query and filtering parameters.
  • FilterFieldRule – a class for simple filtering by field.
  • FilterLogicalRule – a class for logical filtering operations.
  • FilterSerializer – an additional class for serializing a filter into a more compact format than simply through reflection. Not required for use.
  • FilterDeserializer – similar to FilterSerializer.

Checking filter parsing

To check the functionality, you can simply compare two queries: the original one and with the filter from the text query.

Java 
public class CompilerTest {

    private static final ObjectMapper MAPPER = new ObjectMapper()
            .enable(SerializationFeature.INDENT_OUTPUT);

    public static void main(final String[] args) throws JsonProcessingException {
        final var query1 = Query.builder().page(10).size(20)
                .filter(or(
                        isNotNull("_name"),
                        and(eq("_name", "maria"), ilike("_name", "%ari%"), eq("type", "local")),
                        and(eq("_name", "smith"), eq("type", "oidc"))))
                .search("any text")
                .build();

        final var query2 = Query.builder().page(10).size(20)
                .filter(QueryFilterCompiler.compile("""
                        _name is not null
                                or (_name = "maria" and _name ilike "%ari%" and type = "local")
                                or (_name = "smith" and type = "oidc")
                        """))
                .search("any text")
                .build();

        System.out.println(query1.equals(query2));
        System.out.println(MAPPER.writeValueAsString(query1));
    }
}

Comparison of execution speed

When compared in terms of execution speed, JSON deserialization is ~18.5% faster. On the one hand, the difference is big, but on the other, it’s still 70k RPS. Although, it might be worth comparing memory consumption as well.

Text 
Benchmark                     Mode  Cnt      Score   Error  Units
FormulaBenchmark.benchJson   thrpt       86892.340          ops/s
FormulaBenchmark.benchQuery  thrpt       70803.605          ops/s

Conclusion

In general, it would be possible to add not only selection conditions to the request, but also sorting parameters, so as not to transmit separately. Perhaps this is worth doing in the future.

If the project uses Spring Data, then the Query class easily turns into Specification, which can be used in queries. I won’t give an example of the code, since it can be generated by any LLM using the classes specified here and corrected to a working state.

If we consider future use, then for now it will be used in this form in the frame-search module from tekBlueprint.frame to access the REST API and advanced filters from the UI.

ℹ️ Note

Sample project is available in GitHub.

Java Antlr Dsl Parser Filter Devmix
ANTLR4 DSL 2 of 3
← Formula interpreter (DSL) Expression Language (DSL): AST Optimizations and Virtual Machine →