Client-side filter for table on mapped values (JSON objects)


I am fairly new to Retool and have been fiddling around with filtering a specific column in my table.
The data of the table is populated using API requests on an external service and yields the following view

based on the JSON result from the request

  "expenses": [
      "id": 37762092,
      "spent_date": "2022-12-18",
      "notes": "USB-C Ladekabel (Nutzung Apple Netzteil für...)",
      "project": {
        "id": 2996730,
        "name": "Administration, Meetings, interne Prozesse, Diverses",
        "code": "00195"
      "expense_category": {
        "id": 291712,
        "name": "Sonstige",
        "unit_price": null,
        "unit_name": null

Now I would like to filter for the column Category, which is of course a mapped field for the corresponding result set:


However, when I try to apply this filter on the table no records are shown at all.

I am sure that I am probably missing something very basic but I don't get to the issue. Any help is highly appreciated.


try using {{}}

Hmmm, I don't quite get it. Where shall I put this?

I kind of moved ahead and added a multi select component which contains all possible filter values:

Then I added a custom script to that component that looks as follows:

var filters = []
if (multiselectExpenseCategory.value.length>=1) {
  multiselectExpenseCategory.value.forEach(f => {
    filters.push({columnName: "", 
                  filterValue: f, 
                  operator: "equals"})
tableExpenses.setFilters(filters, "or")

Since the multi select component yields the id instead of the name, I also had to adapt the table so that it shows the id in the column Category (the name is now in a custom column category_name):

Filters are correctly applied to the filter section, but they still don't work.

I have tried all sorts of combinations for the columnName in the filter code:,, Category,, etc. But no luck.

Any ideas are highly appreciated.

Solved the issue by adding a custom SQL query as follows:

  {{ }} e
  ( {{ !multiselectExpenseCategory.value.length ? 1 : 0 }} = 1 )  or ( = any ( {{ multiselectExpenseCategory.value.length == 0 ? [''] : multiselectExpenseCategory.value }} ) )