Question on FullTextSearch with field containing URL in F#

[alexmorask]

Hey there,

First off, just wanted to apologize in advance if I'm completely missing something here as I'm not super familiar with how Postgres' built-in text search functions work under the hood.

I'm currently messing around with using Marten in an F# project and I had a quick question on an issue I'm running into. I'm currently trying to use full text searching with an F# record representing an aggregate with this structure:

[<CLIMutable>]
type MealAggregate =
    { Id: Guid
      Name: string
      Protein: string option
      Source: string option
      Created: DateTime
      Updated: DateTime option
      Deleted: DateTime option }

Everything standard with regards to writing and retrieving the data is working as expected. I'm using inline projections and the row is being written correctly to mt_doc_mealaggregate (FSharp.SystemTextJson is being used for serialization of F# types).

My problem is with searching against one specific field of the record. My search functionality is currently written as such:

let private getNonDeletedItemsQuery<'T> (session: IQuerySession) =
    query {
        for aggregate in session.Query<'T>() do
            where (aggregate.MatchesSql("data->> 'Deleted' IS NULL"))
            select aggregate
    }

let search<'T> (documentStore: DocumentStore) (text: string option) =
    let session = documentStore.QuerySession()

    let queryable = getNonDeletedItemsQuery<'T> session

    let result =
        match text with
        | Some parameter ->
            query {
                for aggregate in queryable do
                    where (aggregate.WebStyleSearch(parameter))
                    select aggregate
            }
        | None -> queryable

    task {
        let! aggregates = result.ToListAsync()
        return aggregates |> Seq.toList
    }

As you can see, I'm trying to run a WebStyleSearch on the aggregate record. My problem is that virtually every field of the aggregate is searchable except for the Source field which specifically contains URL's.

Here's an example of a stored projection:

{
    "Id": "402680f5-b29c-4136-be1b-22866a3e4314",
    "Name": "Greek Chicken",
    "Source": "https://example-recipes.com/greek-chicken",
    "Created": "2024-08-15T19:04:51.009561Z",
    "Deleted": null,
    "Protein": "Chicken",
    "Updated": "2024-08-15T19:06:19.331963Z"
}

So in the example above, searching for "greek" would return the record, while searching for "recipes" would not. I thought it might be a problem with F# Option types, but the Protein field is also an option and searching on that works fine as well.

I wanted to check if I was potentially missing some additional configuration or if this might be a limitation of Postgres that I'm not familiar with?

Also, in case it's helpful, here's the creation of my DocumentStore:

DocumentStore.For(fun options ->
        options.Connection(connectionString)
        options.AutoCreateSchemaObjects <- AutoCreate.All

        // Projections
        options.Projections.Snapshot<MealAggregate> SnapshotLifecycle.Inline |> ignore

        // Schema
        options.Schema.For<MealAggregate>().FullTextIndex() |> ignore

        let serializer = Services.SystemTextJsonSerializer()
        serializer.Configure(fun options -> options.Converters.Add(JsonFSharpConverter()))
        options.Serializer serializer)

Thanks in advance!

[bittercoder]

If your using web style search then I would suggest having a look at websearch_to_tsquery and running your search text through that to see what the result vectors look like that are being searched for. Additionally running your source values (the url for example) through to_tsvector and seeing how that is translated.

Under the hood the full text index in postgres tokenises the indexed value. It does break apart urls, but I don't believe it does so in a way that would support your search use case e.g.

select to_tsvector('english', 'https://example-recipes.com/greek-chicken');

Will tokenise as:

'/greek-chicken':3 'example-recipes.com':2 'example-recipes.com/greek-chicken':1

None of those will match on a search term of recipe (or even using a 'starts with' lexeme with prefix matching wont work, because none of the tokens start with the word recipe.

In this instance you might want to change your approach to combine a free-text search with a like %term% type query over just the data->>Source property (though depending on how much data you have that will could be quite slow as it's likely to require a full table scan). Alternatively you could tweak the raw DocumentConfig of the indexes configuration in Marten to manipulate the data->>'Source' property being indexed to break up the url in a way that better fits your search use case (e.g. maybe replacing all non-letter characters with spaces via a regex, so that the individual words in the url like recipe will be searchable and stemmed correctly).

[alexmorask]

@bittercoder Thanks a ton for the response.

With regards to your last suggestion of tweaking the index for the data->>'Source' property, are you referring to something along the lines of using a Calculated Index or adding this transformation elsewhere?

For what it's worth, I tried adding a calculated index to that field just to test it out:

let getSourceIndex = FunctionAs.LinqExpression<MealAggregate, obj> (fun (aggregate: MealAggregate) ->
    match aggregate.Source with
    | Some source ->
        Regex.Replace(source, @"[\W_]+", " ") |> box
    | None -> "" |> box)

options.Schema.For<MealAggregate>().Index(getSourceIndex, (fun x -> x.Name <- "test_index")) |> ignore

(The compiler wasn't playing nicely with automatically converting the F# function to a LINQ expression so I had to do it)

The index creation succeeded, but resulted in the following:

CREATE INDEX IF NOT EXISTS test_index
    ON public.mt_doc_mealaggregate USING btree
    ("((data -> 'Source'::text) ->> 'Value'::text)" COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

which doesn't look like it's completing the Regex transformation I need. I don't see any examples for creating indexes in the docs outside of relatively simple property selection (_.Schema.For<User>().Index(x => x.UserName);), so I assume I'm just trying to do something unsupported.

Either way, greatly appreciate your help.

[bittercoder]

I was more meaning changing the how the fulltext index statement is generated, here's a rough C# example that should hopefully help:

    public static void ConfigureMarten(DocumentMapping<MealAggregate> mapping)
    {
        Expression<Func<MealAggregate, object>>[] expressions =
            [x => x.Id!, x => x.Name!, x => x.Protein!, x => x.Source!, x => x.Created!, x => x.Updated!, x=>x.Deleted!];
        
        var members = expressions
            .Select(FindMembers.Determine).ToArray();

        var regexCleaningDocumentConfig = StringExtensions.Join(members.Select(x => $"REGEXP_REPLACE(COALESCE((data ->> '{x[0].Name}'),''), '[\\W_]+', ' ')"), " || ' ' || ");

        var index = mapping.FullTextIndex("english", expressions);
        var documentConfig = $"({regexCleaningDocumentConfig})";

        index.DocumentConfig = regexCleaningDocumentConfig;
    }

I haven't tested the above code, so there may well be problems - but hopefully this is enough to help you find a potential solution.