How to filter through a nested JSON object in Supbase?

[what.gift]

I have a checkbox variable (boolean) which I’d like to use as a filter on a Supabase query.

I’m unsure how to lookup the column of a nested JSONB object in a filter as the operator available I see “is exactly” and to what the formula might look like, and if this is possible?

Performance is important so I need this to run on the backend (Supabase side) and I know it should be done at the data collection level. I’m hoping this is within the latest SDK release earlier this year.

The docs for filtering is for Xano and the Supabase page doesn’t mention filtering.
Filtering data | WeWeb documentation

[sam1]

I’m looking for pretty much the same thing I found this post so I’m assuming it needs to be done through an endpoint still.

[what.gift]

I was really hoping the Jan 2024 big Supabase update included it as surely accessing and filtering JSON is a really common use case?

* [SUPABASE] Add filters & modifiers in all query actions - was on the release notes.

But it’s missing arrow operators (-> and ->> ) to access nested JSON values, specifying the data type for precise querying from what I can tell.

[what.gift]

I’ve had some success using the “Advanced” fileds instead of “Guided”, as this works:

id,
title,
metadata->gender->>male

Works even with the warning!

However this doesn’t:

id,
title,
(metadata->'gender'->>'male')::boolean = true;

I found in this helpful Supabase SDK video update from WeWeb that all filters should work.

The problem for me is that the code format WeWeb accepts is different to the Supabase Docs which is like this:

const { data, error } = await supabase
  .from('users')
  .select()
  .eq('address->postcode', 90210)

The filters are still a problem as operators at the collection level don’t work. But it does work on the table/frontend view. However, I’d like to query the DB on the backend and need to find the “true” values within this JSON metadata.

[Connor]

After a lot of messing around i was able to crack this.
I couldn’t get the “->” to work for me at all. But i found this on the superbase forum
Supabase Javascript Client - Using filters (specifically the
“Filter referenced tables” item)

Instead of getting your nested item with

table(column)

turn it into an inner join

table!inner(column)

then you can use . notation to filter for it.

Unfortunately this doesn’t work at a collection level, and only seems to work at the workflow level.

[Connor]

I have just come across an alterative solution. Create a view in superbase with the column you want to filter joined. But not apply any filters at the superbase level. The view will basically just be your the table + 1 extra column.

Then in weweb create a new collection and use this new view as your data source. You can then apply filters at a workflow and a collection level. Just remember, when modifying the data to do it against the original dataset, not the view.

[Broberto]

Hello, nice solution For anyone else wondering, I actually wrote an article about this. You actually should be able to use the … spread operator to get the data to the same level in the object, which then allows you to filter it.

Become a Supabase Magician in WeWeb (Tutorial With Examples) – Broberto

By the way, views tend to get slow with many items, because views actually aren’t indexed. So I try to avoid them and use them only for analytical purposes, or some tables that I know will have a few records (such as some user-facing tables etc.) I also use views for things like evaluating things, for example an event’s attendance etc.

[thomlov]

While the spread operator allows you to choose the field for filtering (in a collection), you can’t actually use it to filter, as weweb then tries to filter on the original collection with that column name, and gets an error. I’ve not managed to make it work at least.

[Broberto]

First you spread the joined table via the ... spread operator, in my case I just take the brand_id for this example, I’ll filter on it.

Then you set up the filter on the brand_id to be equal to 132

[thomlov]

Does brand_id exist in the products table by any chance, maybe thats the foreign key?
Try to put a filer on brand_name?

[Broberto]

Yeah now I see it, you’re right. The issue is that WeWeb parses it as a direct column as you said. With the Workflow Actions it works though. You just need to do the [table].column instead of the column directly. Nice catch. I already raised this issue of filtering being different for collections with @Alexis when the actions first came out, it would be amazing to have the same filtering on collections as well.

Recently I just stick to creating my own “collections” workflows as shown in the pic, with the actions.