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

首先跟踪 filterBuilder 函数，以及被视为字符串处理的 PostgreSQL 类型是如何处理的。比较两种提出的 jsonb-to-text 和 text-to-jsonb 相等性方案，包括无效 JSON 和语义等价 JSON 的情况。当所选行为能够防止报告的 jsonb = text 错误，同时不掩盖无效比较时，即视为完成。

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