Extending Filament's QueryBuilder for PostgreSQL JSONB filters

#filament #postgres #laravel

Many Laravel projects using PostgreSQL end up storing part of their data in a JSONB column. The reason is almost always the same: some records have attributes that vary by type, and adding a dedicated column for each variant would make the schema explode. A product might have a color and a material, another a wattage and a voltage, another a file format and a resolution. Rather than adding dozens of columns that are almost always empty, a JSONB attributes column absorbs this variability without a migration for every new case.

This works well on the storage side. It gets complicated as soon as you want to offer filtering on those attributes in a Filament admin interface, with the same comfort as filtering on a regular column.

This article targets PostgreSQL specifically. The JSONB operators and SQL syntax used here (->>, ANY(ARRAY[...]), IS DISTINCT FROM) are PostgreSQL features. MySQL has its own JSON syntax and would require a different approach.

What Filament's QueryBuilder offers out of the box

Filament provides a QueryBuilder component for building visual filters on a table. The user picks a field, an operator (is equal to, is not, is empty, etc.), a value, and Filament translates that choice into a SQL clause with Eloquent.

Two concepts structure this component.

The first is the Constraint: it describes a filterable field. A SelectConstraint for example offers a list of possible values, with search and multiple selection if needed.

The second is the Operator: it's what turns the user's choice into a where clause. The most common one, IsOperator, handles the classic "is equal to" and its inverse, on a single value or on multiple values.

For a field stored in a regular SQL column, everything works without writing a single line of code beyond the constraint declaration:

SelectConstraint::make('category')
    ->label('Category')
    ->options(fn (): array => Product::query()
        ->distinct()
        ->pluck('category', 'category')
        ->toArray())
    ->multiple()
    ->searchable()
    ->operators([
        SelectConstraint\Operators\IsOperator::make()
            ->modifyQueryUsing(fn (Operator $operator, $query) => 
                $operator->apply($query, 'products.category')),
    ]),

The native operator receives the qualified column name, products.category, and builds the appropriate clause itself, whether the user chose one or multiple values, including or excluding them.

Why it breaks as soon as you target JSON

The natural temptation, faced with an attribute stored in a JSONB column, is to give that same IsOperator a path that looks like a column name, something like attributes->color, and hope the rest follows. It doesn't, for three specific reasons that come up in any project mixing Eloquent and JSONB.

The first reason is syntactic. PostgreSQL offers two extraction operators: -> which returns a JSON fragment, and ->>which returns plain text. A generic operator written for regular columns doesn't know which one to use, and doesn't know where to insert it in the where clause either.

The second reason is about typing. A JSONB value compared with = to a string doesn't always match what you'd expect, as long as one side hasn't been explicitly cast to text.

The third reason is the most interesting , because it touches SQL's three-valued logic, a topic many developers encounter in databases without ever really confronting it. In SQL, a comparison can be true, false, or unknown (NULL). When a user chooses "is not equal to this value", they expect all rows that differ from that value, including rows where the attribute is completely absent. But a classic column != value clause silently excludes rows where column is NULL, because NULL compared to anything returns NULL, and a where clause never retains a NULL result. In practice, a "not equal to X" filter seems to swallow rows at random, while it's behaving exactly as SQL intends. For an attribute stored in JSON, this case comes up very often: the key may simply not exist on some records.

Looking for the extension point rather than working around it

Faced with this wall, two paths exist.

The first is to step outside Filament's framework: write ad hoc Eloquent scopes directly on each resource, with whereRawscattered here and there, losing in the process everything QueryBuilder provides for free: the UI, URL serialization of the filter, consistency between different filters in the same application.

The second path is to realize that Filament has probably already anticipated this kind of case. IsOperator is not a black box, it's a regular PHP class, with an apply() method that can be overridden in a subclass, as long as the same contract is respected. Nothing prevents writing your own operator.