c# - Npgsql.PostgresException (0x80004005) : 42883: operator does not exist: jsonb #> text

Question

我有以下 SQL 查询:

select distinct "Customers"."CustomerId"        as "CustomerId",
                "Customers"."FcmRegistrationId" as "FcmRegistrationId",
                "Customers"."FCMServerKey"      as "FCMServerKey",
                "Customers"."AppId"             as "AppId"
from "CustomerEvents"
         inner join "Customers" on "CustomerEvents"."CustomerId" = "Customers"."CustomerId"
where  "Customers"."AdvertiserId" = 16 and "Data" #> '{inner_id}' = '4249699';

它在我的 SQL 编辑器客户端 (DataGrip) 中运行良好。但是当我将它与 C# 一起使用时，我从这个问题的标题中得到了错误。

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: jsonb #> text

我会告诉你我的代码:

public class PartnerApiServices : IPartnerApiService
{
    private readonly ApplicationDbContext _applicationDbContext;

    public PartnerApiServices(ApplicationDbContext applicationDbContext)
    {
        _applicationDbContext = applicationDbContext;
    }

    public IQueryable<CustomerForPartnerApiServiceModel> GetCustomerBySearchField(Advertiser advertiser, string searchValue)
    {
        var rawSql = @"
            select distinct ""Customers"".""CustomerId""        as ""CustomerId"",
                            ""Customers"".""FcmRegistrationId"" as ""FcmRegistrationId"",
                            ""Customers"".""FCMServerKey""      as ""FCMServerKey"",
                            ""Customers"".""AppId""             as ""AppId""
            from ""CustomerEvents""
                     inner join ""Customers"" on ""CustomerEvents"".""CustomerId"" = ""Customers"".""CustomerId""
            where  ""Customers"".""AdvertiserId"" = @AdvertiserId and ""Data"" #> @SearchField = @SearchValue"; 

        var res = _applicationDbContext.BySearchFieldCustomerApiModels.FromSql(rawSql,
            new NpgsqlParameter("SearchField", advertiser.SearchField),
            new NpgsqlParameter("SearchValue", searchValue),
            new NpgsqlParameter<int>("AdvertiserId", advertiser.AdvertiserId));

        return res;
    }
}

关于如何正确传递 SearchField 和 SearchValue 有什么想法吗？

Answer 1

要使用文本参数，而不是使用 #>，请使用 #>>。前者需要一个 jsonb 参数，而后者需要一个 text 参数 ( see the PostgreSQL docs )。

您的代码在 DataGrip 中运行的原因是 '{inner_id}' 是直接嵌入到您的 SQL 中的无类型文字，因此 PostgreSQL 将其隐式转换为 jsonb。然而，当使用 Npgsql 的参数时，Npgsql 发送类型化的 text 参数(Npgsql 的参数(几乎)总是类型化的))，因此 PostgreSQL 提示不匹配。