Query builder to filter data in TypeScript

I wrote posts (1, 2, 3 and , 4) about my learnings creating table components, and this post can be the third part as I made a query builder for that component, but it can be used in other uses cases.

A query builder provides a convenient and (usually) simple interface for creating and executing queries (filtering) on a data set.

In TypeScript (or any other language) if you want to filter an array of objects like:

const data = [
  { field1: 1, field2: "a", field3 ....},
  { field1: 2, field2: "b", field3 ....}
  ...
]

We must write a code like this, with the logic to do the filtering:

const filteredData = data.filter(row => {
  return row.field1 > 10 && row.field2 === "b" || ....
})

The goal is to define a dynamic way to define the filters without changing the code.

Characterizing a filter

A simple filter, is basically, a value source (the attribute name), a value to compare with and the comparator operator.

Using Typescript we can model that like:

NOTE: I’m not going to do complex typing to simplify the code, but in production is necessary to type the field to ensure is a key of the data and the value type

type FilterComparationOperator = 'eq' | 'neq'| 'gt' | 'gte' | 'lt' | 'lte' | 'contains' | 'notContains' | 'startsWith' | 'endsWith'
interface Filter {
  field: PropertyKey
  value: unknown
  operator: FilterComparationOperator
  caseSensitive?: boolean
}

First, we model in the type FilterComparationOperator the different ways to compare the field value and the filter value:

equal (eq):
non-equal (neq)
great than (gt)
great than or equal (gte)
less than (lt)
less than or equal (lte)
contains the value (contains)
non-contains the value (notContains)
starts with the value (startsWith)
endsWith (endsWith)
You can think more operators if you need it

Then we model the filter, with the field we will use to the the value in the data, the value for the filter, the comparator operator, and a couple of flags to refine the filter behavior, if it should be case-sensitive (case-insensitive by default)

With that, we will write a function to compare 2 values using the operator and the filter constraints like if the comparative should be case-sensitive or non-case-sensitive:

function compare<T = unknown>(
  value: T,
  operator: FilterComparationOperator,
  filterValue: T,
  caseSensitive = false
): boolean {
  function isString(value: unknown): value is string {
    return typeof value === "string";
  }

  // If the filter value is a string and the filter is not caseSensitive
  // converts the values to lowercase for a case insensitive comparison
  const filterValueComp =
    isString(filterValue) && !caseSensitive
      ? filterValue.toLocaleLowerCase()
      : filterValue;
  const valueComp =
    typeof value === "string" && !caseSensitive
      ? String(value).toLocaleLowerCase()
      : value;

  // Checks if we can do numeric comparations with the values
  function isComparable(value: unknown): value is string | number {
    return typeof value === "string" || typeof value === "number";
  }

  type OrString<TValue> = TValue | string;

  const comparationFuncs: Record<
    FilterComparationOperator,
    (value: OrString<T>, filterValue: OrString<T>) => boolean
  > = {
    // Note the == instead of ===. We want to allow to compare 1 and "1" as the same value
    eq: (value, filterValue) => value == filterValue,
    neq: (value, filterValue) => value != filterValue,
    contains: (value, filterValue) =>
      String(value).includes(String(filterValue)),
    notContains: (value, filterValue) =>
      !String(value).includes(String(filterValue)),
    startsWith: (value, filterValue) =>
      String(value).startsWith(String(filterValue)),
    endsWith: (value, filterValue) =>
      String(value).endsWith(String(filterValue)),
    gt: (value, filterValue) =>
      isComparable(value) && isComparable(filterValue)
        ? value > filterValue
        : false,
    gte: (value, filterValue) =>
      isComparable(value) && isComparable(filterValue)
        ? value >= filterValue
        : false,
    lt: (value, filterValue) =>
      isComparable(value) && isComparable(filterValue)
        ? value < filterValue
        : false,
    lte: (value, filterValue) =>
      isComparable(value) && isComparable(filterValue)
        ? value <= filterValue
        : false,
  };

  return comparationFuncs[operator]
    ? comparationFuncs[operator](valueComp, filterValueComp)
    : false;
}

console.log(compare(12, "gt", 8)); //True
console.log(compare("lorem ipsum dolor est", "contains", "DOloR")); //True
console.log(compare("lorem ipsum dolor est", "contains", "DOloR", true)); //False

Having multiple filters and complex relationships between them

We want to let the user create complex comparatives like if the (temperature is lower than 20 and temperature is higher than 0) or the temperature is -999 or ((the city name is Vigo and country is Spain) or (city name is Santiago and the country is chile))

Note the parenthesis that groups the logic as is not the same A and B or C (same as (A and B) or C) than A and (B or C)`. Read more about the order of operations

So we need to define a struct to model these groups and the relationship.

interface GroupFilter {
  filters?: Filter[]
  groups?: GroupFilter[]
  operator: 'and' | 'or'
}

This “filter group” can contain filters and other groups (that can contain more filters and more groups,….) and the operator to define the relationships between the filters and groups. For example:

const group: GroupFilter = {
  operator: "or",
  filters: [{ field: "temperature", value: 20, operator: "lt" }, { field: "temperature", value: 0, operator: "gte" }],
};

defines a filter group that checks if the temperature is lower than 20 OR higher (or equal) than 0.

With this simple nested struct, we can create complex filters like the one in the example:

// if the (temperature is lower than 20 and temperature is higher than 0) or the temperature is -999 or ((the city name is Vigo and country is Spain) or (city name is Santiago and country is Chile))

const filters: GroupFilter = {
  operator: 'or',
  filters: [{ field: 'temperature', operator: 'eq', value: -999 }],
  groups: [
    {
      operator: 'and',
      filters: [
        { field: 'temperature', operator: 'lt', value: 20 },
        { field: 'temperature', operator: 'gt', value: 0 },
      ]
    },
    {
      operator: 'or',
      groups: [
        {
          operator: 'and',
          filters: [
            { field: 'city', operator: 'eq', value: 'Vigo' },
            { field: 'country', operator: 'eq', value: 'Spain' },
          ],
        },
        {
          operator: 'and',
          filters: [
            { field: 'city', operator: 'eq', value: 'Santiago' },
            { field: 'country', operator: 'eq', value: 'Chile' },
          ],
          
        }
      ]
    },
  ]
}

The last piece we need is a function to filter the data rows:

function filter<T extends Record<PropertyKey, any>>(
  data: T[],
  filterGroup: GroupFilter
): T[] {
  // If the no rows, we don't need to continue
  if (data.length === 0) {
    return data;
  }

  // Check if a row fulfill the constraints
  const filterRow = (row: T, filterGroup: GroupFilter | undefined): boolean => {
    // Exit condition for this recursive function
    if (!filterGroup) {
      return true;
    }
    // Checks if a col in the row matches a filter
    const filterColFunc = (filter: Filter): boolean => {
      // If the row has no a field with the field name continue
      if (!row[filter.field]) {
        return true;
      }

      return compare(
        row[filter.field],
        filter.operator,
        filter.value,
        filter.caseSensitive
      );
    };

    const groups = filterGroup.groups || [];
    const filters = filterGroup.filters || [];

    if (filterGroup.operator === "and") {
      // For the and operator, we should return true if all of the filters and groups are fulfilled. If no filters or no groups we consider it a match
      return (
        (filters.length === 0 || filters.every(filterColFunc)) &&
        (groups.length === 0 ||
          // For the groups we apply again the filterRow function to get the result for the group
          groups.every((group: GroupFilter) => filterRow(row, group)))
      );
    } else {
      // For or operator, we should return true if any of the filters or groups fulfill or they are empty
      return (
        filters.some(filterColFunc) ||
        groups.some((group: GroupFilter) => filterRow(row, group)) ||
        (filters.length === 0 && groups.length === 0)
      );
    }
  };

  // Loops the rows and filter them
  return [...data].filter((row) => filterRow(row, filterGroup));
}

Now we can execute our filter just by doing:

const filteredData = filter(data, filters)

Putting all together

Run it in TS playground

Last thoughts

The code I show you here is just a base, there are improvements it needs to be production-ready, for example the typings, the comparison between numbers and strings (now 1 !== “1” and maybe you want to consider that is the same for filtering porpoises), the performance, etc.

But my goal is to show you how simple is to create a dynamic filter that allows the user to define how to filter the data, for example, the user can generate the filter groups using a query builder UI or just a form to select a couple of value, but the developer can define the operators depending on the field without the need to write it in the code.

Read the code carefully and check how powerful are the recursive functions, they can seem complicated in the beginning, but when you start to understand them, they make the code simpler and more flexible.

Back to home
Sergio Carracedo

Sergio Carracedo