CODEWITHSUNDEEP
c#postgresql.net-corenpgsql
Aleksej_Shherbak
Aleksej_Shherbak

Reputation: 3088

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

I have the following SQL query: 

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';

It works nice in my SQL-editor-client (DataGrip). But when I use it with C# and I have the error from the title of this question.

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

I will show you my code: 

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;
    }
}

Any ideas how I can pass SearchField and SearchValue correctly?

Upvotes: 1

Views: 2952

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247950

Add an explicit type cast so that PostgreSQL knows that the string literal is to be interpreted as array:

var rawSql = @"
            ...
            where  ""Data"" #>> CAST(@SearchField AS text[]) = @SearchValue";

Upvotes: 1

Shay Rojansky
Shay Rojansky

Reputation: 16742

To use a text parameter, instead of using #> use #>>. The former expects a jsonb parameter, whereas the latter expects a text parameter (see the PostgreSQL docs).

The reason your code works in DataGrip is that '{inner_id}' is an untyped literal embedded directly in your SQL, so PostgreSQL implicitly casts it to jsonb. However, when using a parameter with Npgsql, Npgsql sends a typed text parameter (Npgsql's parameters are (almost) always typed)), and so PostgreSQL complains about the mismatch.

Upvotes: 2

Related Questions