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

Comienza rastreando la función filterBuilder y cómo se manejan los tipos de PostgreSQL tratados como cadenas. Compara los dos enfoques de igualdad propuestos jsonb-to-text y text-to-jsonb, incluidos los casos de JSON no válido y semánticamente equivalentes. Se considera terminado cuando el comportamiento elegido evita el error reportado jsonb = text sin ocultar las comparaciones no válidas.

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