Filtering a Vector Result by Two Dates

I have a supabase database which contains normal db fields plus an embedding, plus metadata.

I wish to query this database to find matching vectors, but I first want to filter the results based on the datetime column.

So, for example, I would retrieve all results between Jan 1st and Jan 5th. Then I would query those vectors only.

How do I apply this filter to either the metadata or the database fields when querying the database?

In the Supabase Vector Store node there’s an option to filter based on metadata, but it seems to be fixed variables. I.e. it must contain “X”. There doesn’t appear to be a way to set a date range or any other more complex query.

Is there a solution or do I need to do it using manual HTTP requests?

Here’s my workflow so far:

It looks like your topic is missing some important information. Could you provide the following if applicable.

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:
  • n8n version: 1.34.0
  • Database: Supabase
  • Running n8n via n8n cloud

Hey @James_Pardoe,

I am not that familiar with the Supabase vector store but looking at the API docs the filter seems to want key / value pairs. Have you already tried setting a name and using some json in the value to see if that changes the outcome?

Hi @Jon - thanks for your reply.

The issue is that I’m looking for a range of dates, rather than one specific date. How might I specify that in the value field?

Anyone got any ideas on this? I’m hitting a brick wall. There needs to be a way to specify “greater than, less than, etc” in the filters.

The Langchain Supabase documentation specifies the format for filtering metadata:

const funcFilterB: SupabaseFilterRPCCall = (rpc) =>
    rpc
      .filter('metadata->b::int', 'lt', 3)
      .filter('metadata->c::int', 'gt', 7)
      .filter('metadata->>stuff', 'eq', 'right')

However the Vector Retriever node only allows a name, value pair.

I have tried all kinds of things for the value, such as:
“gt”, {{ $(‘Code’).item.json.data.startTime }}
.$gt{ {{ $(‘Code’).item.json.data.startTime }} }

But none of them return any data.

I have also tried tweaking the name value, from timestamp to:

timestamp.$gt

But still no luck.

The only thing that works is specifying an exact value, which isn’t much use when trying to filter by date ranges.