Efcore.pg: Update keys inside JSONB columns with migrations apply

[sar]

By default, changes to class properties do not affect the data inside [ColumnType(TypeName = "jsonb"]) entities resulting in a loose structure in the database and requiring manually invasive PgSQL queries to correct.

For example,

public class FooBar {
     [ColumnType(TypeName = "jsonb")]
     public Foo Foo { get; set; }
}

```csharp
public class Foo {
public string Bar { get; set; }
}

Updated with new properties...
```csharp
public class Foo {
     public string Bar { get; set; }
     public int Baz { get; set; }
     public DateTimeOffset Qux { get; set; }
}

Results in a migration with changes that do not modify the keys inside jsonb columns (i.e. keys baz and qux are not automatically added to existing json data).

Expected Behaviour

• Use postgres update table constructs to modify schema inside jsonb
• Function similarly to traditional column insertion on migration apply when new properties are created

Is there an overriding or extension method call for performing this on dotnet ef database update?

[roji]

@sar given that JSON itself isn't strongly typed and has no schema, what does it mean exactly to "modify schema inside jsonb"? What exact operation would you expect EF Core to send for you?

[NinoFloris]

I read it as them wanting the virtual schema (in this case the CLR type) of ef core json columns to be strongly consistent with the actual shape of all the data in the database.

As there is no DDL for json in postgres, the expectation is full data transformation DML statements in the migration to keep the data shape consistent with their code. (These migrations would be very write heavy, rewriting all rows/toasted values)

It's a huge amount of work that I think is quite unrealistic (and I would love this feature as well!) to expect for the foreseeable future.

[roji]

I read it as them wanting the virtual schema (in this case the CLR type) of ef core json columns to be strongly consistent with the actual shape of all the data in the database.

What would that actually mean, except maybe removing JSON elements when the corresponding CLR property is removed? Would there be any value in adding empty JSON elements when a property is added, given that when loading the JSON, an empty or non-existing element give the same result?

Yeah, in general I don't think this is very realistic (or even that valuable...)

[NinoFloris]

Well ideally it'd be more than just removal of course, but also change of type/shape from array to object and such things, again to actually be able to specify this in a model is already a ton of work :)

[roji]

I know it's an academic discussion, but still :) What kind of DML would change existing JSON array values to object? I mean, even PostgreSQL doesn't allow to change arbitrary column types - you have to specify a transformation yourself. In the same way, I can't see what automatic transformations we'd be able to do... though of course users can always add a quick JSON update in their migrations via raw SQL, which applies the exact logic they want.

[roji]

Continued discussion offline with @NinoFloris, we don't think there's something of value (or practical) for us to do here. Will keep this open for a bit more to see if @sar has anything else to say.

[sar]

As there is no DDL for json in postgres, the expectation is full data transformation DML statements in the migration to keep the data shape consistent with their code. (These migrations would be very write heavy, rewriting all rows/toasted values)

Well ideally it'd be more than just removal of course, but also change of type/shape from array to object and such things, again to actually be able to specify this in a model is already a ton of work :)

I know it's an academic discussion, but still :) What kind of DML would change existing JSON array values to object? I mean, even PostgreSQL doesn't allow to change arbitrary column types - you have to specify a transformation yourself.

Great discussion as the performance implications and complexity of covering all possible use cases of transformations makes sense.

Think it'll be easier for users to implement specific sql operations in the csharp project to validate consistency, apply transforms on jsonb data to align with type changes, relative to the code-first properties.

Thanks @roji and @NinoFloris for the explanation, closing this one out!