JSON.NET plugin

[roji]

With the new JSON.NET plugin coming to Npgsql 3.3, we need to create the corresponding plugin for the EF Core provider (depends on #300). It would provide a db context options extension, UseJsonNet(), which would do the following:

• Add the Npgsql JsonNet plugin to the global type mapper
• Add the appropriate RelationalTypeMapping to the NpgsqlTypeMappingSource
• Add the appropriate translators for JSON operations.

[roji]

After some research into this, there's one very annoying mismatch between Newtonsoft.JSON and PostgreSQL's JSON support that makes this difficult. Mapping JObject and JArray is easy enough, but operation translation is problematic.

In Newtonsoft., the way to access a string value in a JSON is:

string itemTitle = (string)rss["channel"]["item"][0]["title"];

It should be easy to map the indexers into PostgreSQL's -> operator (see the docs). However, the cast of the last value into string is problematic... In Newtonsoft, this cast yields the simple string value, but PostgreSQL treats it as a cast of a JSON document, so returns the string value surrounded by double-quotes. In other words, if the string value being access is foo, Newtonsoft will return foo while PostgreSQL will return "foo".

To work around this, we need to customize the casting behavior when it is applied to a json/jsonb. The Newtonsoft casting behavior can be implemented by rendering #>>'{}', which "looks up" the root element of the give JSON document and returns it as a string. However, it's difficult to see how we can override this behavior in NpgsqlQuerySqlGenerator from a plugin.

In theory we can replace the NpgsqlQuerySqlGenerator service from the plugin, although replacing a service with another service wrapping the first is really non-trivial with the dependency-injection system. Npgsql could register a factory lambda for IQuerySqlGenerator, which the JSON extension would replace with another ServiceDescriptor the does the wrapping.

However, if the replacement service extends NpgsqlQuerySqlGenerator, overriding only the relevant cast behavior, it's impossible to have multiple plugins changing bits of behavior in the SQL generator (each one would clobber the current generator with its own).

Because of the above complications I'm going to drop this for now and revisit later. In any case PG 11 is supposed to add SQL/JSON support, maybe it's better to wait for that and see what things look like.

[seriouz]

Probably @JamesNK has some ideas on this topic?

[JamesNK]

If you want double quotes then use https://www.newtonsoft.com/json/help/html/M_Newtonsoft_Json_Linq_JToken_WriteTo.htm with a JsonTextWriter over the top of a StringWriter. Or manually check for a string and update the value with a call to JsonConvert.ToString(s) (I think).

[roji]

Thanks @seriouz and @JamesNK.

The problem isn't really with Newtonsoft.JSON or how to do stuff with it... The goal of this plugin would be to translate idiomatic, simple JSON.NET code to PostgreSQL JSON operators in SQL. The problem is that there's an annoying mismatch between the regular way to do JSON.NET and the PostgreSQL operators where it comes to string casts. In other words, I'm sure I can write JSON.NET code that produces the double quotes, as you suggest above @JamesNK, but the point is to allow users to write normal, regular JSON.NET and for that to get evaluated seamlessly and out of the box on PostgreSQL.

Concretely, I want to translate the following JSON.NET sample:

string cpu = (string)o["CPU"];

The expression o["CPU"] in this sample is a JSON object (JToken in JSON.NET). But when we cast to string in JSON.NET, we simply get the string (no quotes), whereas in PostgreSQL we get quotes, since PostgreSQL considers that a text cast of a JSON object needs to be a valid JSON string representation.

So the problem isn't how to get JSON.NET to produce the quotes, but rather how to properly translate JSON.NET code to PostgreSQL in a way that everything works.

[markusschaber]

Hi, roji,
AFAICS, it should be possible to extract the string as string using json_extract_path_text() instead of using the cast, see https://www.postgresql.org/docs/10/static/functions-json.html.

[roji]

@markusschaber but when would you call that function? The point is that the user wrote the expression (string)o["CPU"], and that's what you have to translate to SQL... You're right that we could translate the C# cast operation to json_extract_path_text() (or to the #>>'{}' operator as I suggested above, although you're proposal's better), but that would involve changing the translation for casts, which is problematic from a plugin, as I wrote above.

[greenboxal]

What's the current status on this? Can we contribute somehow?