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

[piaste]

trafficstars

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

[Thorium]

fieldNotation is how it serializes the field. It has a parameter SqlColumnType where is a column type, at some level. The problem is of course that if you use a canonical opreations like (my_datetime_column).AddDays(4).ToString().StartsWith("22") then the return type of the expression is totally different from the return type of the column.

Postgres is going towards NoSQL with these JSON things, if we ever do a provider support for CosmosDB, there are probably similarities.

[piaste]

Unfortunately, unless I'm mistaken, the SqlColumnType type doesn't actually contain the column type, as in varchar, jsonb, etc.. Its definition is:

and SqlColumnType =
    | KeyColumn of string
    | CanonicalOperation of CanonicalOp * SqlColumnType
    | GroupColumn of AggregateOperation * SqlColumnType

And yeah, there's a lot of json features in Postgres, but I don't think supporting those is particularly important, because if you need to use them inside Linq you can simply expose them as regular stored procedures, and most should work just fine.

This issue on the other side is annoying because if SqlProvider abstracts a database type as a string (there's a few others, not just json/jsonb), and comparing the column to a string value typechecks successfully in F#, then the user will expect the comparison to typecheck in Postgres as well.

[Thorium]

True, fieldNotation has only the .NET type (String in this case)

[Thorium]

Silly question, what if you do:

query { for x in xs where x.MyJSonColumn.ToString() = "test" }

That will do the comparison as text, not in Json. Will that matter (e.g. whitespaces)?

[piaste]

Assuming that calling .ToString() on a string doesn't get optimized away.., then yes, the current implementation would translate it to ::varchar, which is the same as ::text, so you essentially get the second kind of equality:

select ('{"x": 1, "y": 2}'::jsonb)::varchar = '{"x": 1, "y": 2}'; // true
select ('{"x": 1, "y": 2}'::jsonb)::varchar = '{"y": 2, "x": 1}'; // false
select ('{"x": 1, "y": 2}'::jsonb)::varchar = '{"x": 1,     "y": 2}'; // false
select ('{"x": 1,     "y": 2}'::jsonb)::varchar = '{"x": 1, "y": 2}'; // true

Whereas:

select '{"x": 1, "y": 2}'::jsonb = '{"x": 1, "y": 2}'::jsonb; // true
select '{"x": 1, "y": 2}'::jsonb = '{"y": 2, "x": 1}'::jsonb; // true
select '{"x": 1, "y": 2}'::jsonb = '{"x": 1,     "y": 2}'::jsonb; // true
select '{"x": 1,     "y": 2}'::jsonb = '{"x": 1, "y": 2}'::jsonb; // true

For the sake of completeness, this is one of the few user-side differences between the old json type, which stores values in the original text format, and the new jsonb format which stores the parsed JSON. Casting to text probably makes sense for json, since caring about the original formatting is the main reason to use it in new projects.

[Thorium]

What could be done is:

• Custom method e.g. ParseJSon(x) for strings. The method can be a function in SQLProvider namespace like the current e.g. let ParseJson (a:'a) = "". Or maybe an extension method for strings would be better, x.JsonEquals(y). The implementation is not important, you need just a stub that you can find from the Linq-expression-tree.
• Then create an active pattern that finds that function. Add one new item to CanonicalOp for that, e.g. JsonEq with a string parameter. In case of extension method, it would be very similar to E.g. x.IndexOf(y) here.
• And then just match the Postgres operation to your sprintf '(%s=%s::jsonb)' column (fieldParam search) like this. I don't know if any other provider supports JSON.
• Now you can use it inside all the where-clauses, joins, etc.

[piaste]

Thank you for the hints, I will give that a try.

I don't know if any other provider supports JSON.

MySql, Oracle, and MSSQL have all added various levels of native JSON support with their latest releases, though I haven't had experience with them. SQLite also has a JSON extension but my understanding is that it's just a set of helper functions, otherwise it still treats it as plain text.

[koliber]

A Namely profile with the email [email protected] was present in the import data from April 19th through April 228th.

The user was created in our system on April 20th, 2018, from Namely.