[PostgreSQL] Comparing jsonb columns to string values in WHERE statements

Start by tracing the filterBuilder function and how PostgreSQL types treated as strings are handled. Compare the two proposed jsonb-to-text and text-to-jsonb equality approaches, including invalid and semantically equivalent JSON cases. Done means the chosen behavior prevents the reported jsonb = text error without hiding invalid comparisons.

... results in an error "operator does not exist: jsonb = text".

This is because the filterBuilder function does not add a type cast for PostgreSQL types which are treated as strings .NET-side, such as jsonb.

Workaround: define a jsonb -> text -> bool equality function in your database schema, then associate it to the = operator (or invoke it explicitly).

However, there are two ways to define this equality:

• Cast the jsonb column to text. This will never fail, however it may return false if the resulting text differs from the parameter in semantically-insignificant ways, such as whitespace, or the ordering of JSON properties

• Cast the text value to jsonb. This will perform a proper semantic comparison, but will throw an exception if the text isn't valid JSON - and I can imagine instances where you may want to check an unknown (user-provided?) text against a stored JSON value. Note that an empty string is not valid json.

If I implement this in a PR (which may be tricky, as filterBuilder doesn't seem to have access to the column type), which approach do you think should be taken? I strongly believe that the latter is the better one (because any failures will be immediately obvious rather than stealthy, and because both F# and PostgreSQL have a culture of correctness over permissiveness), but I'm throwing it out there in case people have different opinions.

Below an example of the workaround above, for anybody who may run into this issue:

create or replace function jsonb_compare(j jsonb, t text)
    returns bool 
    as $$ select (j=t::jsonb)    
    $$ language sql;

create or replace function jsonb_compare_2(t text, j jsonb)
    returns bool 
    as $$ select (j=t::jsonb)    
    $$ language sql;

create operator = (
    leftarg = jsonb,
    rightarg = text,
    procedure = jsonb_compare,
    commutator = =
);

create operator = (
    leftarg = text,
    rightarg = jsonb,
    procedure = jsonb_compare_2,
    commutator = =
);