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

Inizia tracciando la funzione filterBuilder e il modo in cui vengono gestiti i tipi PostgreSQL trattati come stringhe. Confronta i due approcci di uguaglianza proposti jsonb-to-text e text-to-jsonb, inclusi i casi di JSON non validi e semanticamente equivalenti. Il lavoro è completato quando il comportamento scelto previene l’errore segnalato jsonb = text senza nascondere i confronti non validi.

... 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 = =
);