Access properties inside Jsonb field for Npgsql

PostgreSQL has a Jsonb data type that allows you to add additional properties to the standard relational model with the ability to search through them.

EntityFramework Core with Npgsql extension can pull field data to System.String



type

However, to filter by Json properties through EF at the query level, you have to use pure SQL, which is not very convenient, since you need to go into mapping (if it is not automatic), look for the names of the fields that correspond to the properties of the models, support this naming. The flexibility that ORM gives us is lost.

If it depresses you, as well as me, welcome to cat.

At the end of the article there is a link to the source!

Denote the tasks

As a developer, I want to have a tool to access Jsonb fields with the goal of filtering and sorting by them, which:

• It will be compatible with EntityFramework Core 2 (we use it)
• It will not require you to write SQL yourself while working with it
• Will work with flat Json structure (inside json there are only json properties)

I ’ll add that there is Npgsql.Json.NET , which can project Json and Jsonb values ​​into CLR types. To be honest, I don’t understand what it might be for, because since we needed a Json field in a relational database, most likely we have entities with a dynamic set of fields.

The algorithm for solving the problem

1. Define a method (or methods) that will cover our needs.
2. Create a translator that will participate in the generation of SQL code.
3. Screw it all to Npgsql.

Decision

First, we define a method. I want it to be used something like this:

 context.Entity.Where(x => JsonbMethods.Value<string>(x.JsonbField, "jsonPropertyName") == "value")
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Therefore, here is our method:

 public static TSource Value<TSource>(object jsonbProperty, string jsonbPropertyName) { throw new NotSupportedException(); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

For several hours I was picking the sources of EF Core, Npgsql and not only in search of ways to extend the basic functionality of SQL generation. I got to this article , but I did not like the author’s approach to the method of connecting the translator, because it redefines the standard tool, which means it can conflict with another similar tool.

As a result, I got to the source of Net Topology Suite. All I needed from there was a way to connect a method translator.

But most of the time I spent on generating the sql fragment I needed.

Here is the required syntax

tableAlias."JsonField"->>"insideProperty"

At first I tried in the translator to return ColumnExpression. The first parameter when creating it is the column name (string). I just cooked it up from the parameters that come to me in the method. Launched, checked, error. It turns out that what I pass as a name is wrapped in quotation marks. As a result, SQL turned out to be tableAlias.""JsonField"->>"insideProperty""



.

In the source code of the generator, I found the VisitColumn



method in which this behavior was hardcode and did not depend on any parameters. That is, I could not affect it. It was necessary to look for another solution.

Then I created my own Expression



- JsonbPropertyAccessorExpression: Expression

It remains to override its Accept



method for ISqlExpressionVisitor



.

But the trouble is, in this interface there is no method that a custom operator could segment. Then it occurred to me to visit not one method, but several. First visited VisitColumn



, which created access to the tableAlias. "JsonField" column, then VisitSqlFragment



, into which I threw "->>'insideFieldName'"



.

I did not hope, but it worked. Nearly.

When I tried to filter by text for exact coincidence, for some reason, such a tableAlias."JsonField"->>"insideProperty" = JSONB "value"



filter was tableAlias."JsonField"->>"insideProperty" = JSONB "value"



, which caused an error, since it is impossible to cast text to JSONB type if it does not contain valid Json . And why do I need to lead something to something when I want a text?

I even made the decision to remove the mark from the Jsonb column from the mapping model, that it is Jsonb, adding only this mark to the MigrationContext



so that it generates the correct migrations. And it even took off, but the approach seemed to me a crutch. Nevertheless, I stopped there.

After that, I set to CAST, because the Value



method is universal and there can be various types of data in the Json properties, which also need to be sorted and filtered.

As a result, I began to return ExplicitCastExpression



from my translator, into which I passed my custom Expression



and the type that was contained in the universal arguments of the Value



method.

when I looked at the resulting SQL when searching by date, I found that the compared value was cast to the timestamp type. timestamp 'some date value'



. And then it dawned on me. The previous problem, which I solved with a crutch, went away by itself. When the accessor was cast into the text to the Json field, the generator no longer added an explicit conversion to JSONB, because the comparison operation already had text on the left, and by default the accessor of the Jsonb field returns the Jsonb type.

At the end

In conclusion, I want to add that I did not find documentation on how to add custom translators of properties and methods. probably badly searched. If someone has comments on the approach, on the code, etc., write in the comments.

If someone wants to expand the library in forks, write in a personal letter, I will try to help. Well, or throw pullrequests.

Here is the link to the source