ts_vector_update_trigger with JSONB column in postgresql / npgsql + EFCore

Question

In EFCore + npgsql, using postgresql 10, I want to create a tsvector column to allow full text search of some fields in a model:

public class MyModel
{
  public string Title { get; set; }

  public string Description { get; set; }

  [Column(TypeName = "jsonb")]
  public string JSON { get; set; }
}

I want the full text search to include values from the JSONB column, so as according to the npgsql docs I update my model like so:

public class MyModel
{
  public string Title { get; set; }

  public string Description { get; set; }

  [Column(TypeName = "jsonb")]
  public string JSON { get; set; }

  public NpgsqlTsVector SearchVector { get; set; }
}

add the following index in OnModelCreating in my DB context:

  modelBuilder.Entity<MyModel>(m =>
  {
    // create some other indexes
    m.HasIndex(e => new { e.SearchVector }).ForNpgsqlHasMethod("GIN");
  });

Then create the migration and edit (in accordance with docs) to get the following methods:

protected override void Up(MigrationBuilder migrationBuilder)
{
  migrationBuilder.AddColumn<NpgsqlTsVector>(
    name: "SearchVector",
    table: "MyModels",
    nullable: true);

  migrationBuilder.CreateIndex(
    name: "IX_MyModels_SearchVector",
    table: "MyModels",
    column: "SearchVector")
    .Annotation("Npgsql:IndexMethod", "GIN");

  migrationBuilder.Sql(
    @"CREATE TRIGGER my_model_search_vector_update BEFORE INSERT OR UPDATE
    ON ""MyModels"" FOR EACH ROW EXECUTE PROCEDURE
    tsvector_update_trigger(""SearchVector"", 'pg_catalog.english', ""Title"", ""Description"", ""JSON"");");

  // I am updating an existing table, so:
  migrationBuilder.Sql("UPDATE \"MyModels\" SET \"Title\" = \"Title\";");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
  migrationBuilder.DropIndex(
    name: "IX_MyModels_SearchVector",
    table: "MyModels");

  migrationBuilder.DropColumn(
    name: "SearchVector",
    table: "MyModels");

  migrationBuilder.Sql("DROP TRIGGER my_model_search_vector_update");
}

But after all this, when applying Update-Database, I see:

Failed executing DbCommand (50ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
UPDATE "MyModels" SET "Title" = "Title";
Npgsql.PostgresException: column "JSON" is not of a character type

I assume this is because the JSONB column is binary data. Is it possible to achieve what I'm looking for? I'm relatively new to postgresql, npgsql and EFCore.

Shay Rojansky Shay Rojansky · Accepted Answer · 2019-08-09T16:39:19

I'm pretty sure you can't do full-text search over PostgreSQL jsonb because, as you wrote, it's not a text type. There's a small change that using json would work - as that's textual storage - but that type is much closer to simple text and doesn't offer many of the advantages of binary jsonb.

However, depending on exactly what you want to do, jsonb has quite a lot of search functionality (with SQL/JSON JSONPATH also coming in PostgreSQL 12). This functionality isn't currently mapped in EF Core, but that shouldn't really stop you from using it via raw SQL.

ts_vector_update_trigger with JSONB column in postgresql / npgsql + EFCore

1 Answers