Is it possible to have a proxy property for json/jsonb field for LINQ-query purposes?

[eSPiYa]

This json data field is unstable but with some fields that seems exists in all instances. These json data were extracted from a third-party service tool so we don't have any control on it. I want to preserve the original data while having a proxy property just for LINQ-querying because using JsonDocument mapping is quite tedious, some fields that I need are from great grandchild: var records = this.dbSet.AsQueryable().Where(r => r.Data.RootElement.GetProperty("Properties").GetProperty("MetaData").GetProperty("Provider").GetProperty("Name").GetString() == "AWS").ToList();

I tried to create an unmapped property as proxy(MappedData) by ignoring it in fluent API(I wrote these codes by hand because my work laptop got limited internet connection): class Log { public Guid Id {get;set;} public DateOnly Date {get;set;} public JsonDocument Data {get;set; public MappedJsonData MappedData{ get => JsonSerializer.Deserialize(this.Data); set => this.Data = JsonSerializer.SerializeToDocument(value); } }

It translates fine to the result, but not if used in filtering like in Where clause: var records = this.dbSet.AsQueryable().Where(r => r.MappedData.Properties.MetaData.Provider.Name == "AWS").ToList(); I'm getting an error related to MappedData that it can't get translated because it is unmapped.

I tried to use Newtonsoft's JSON.NET because it seems easier to perform LINQ queries and not that tedious. Even after I applied the extension UseJsonNet() and replaced the type of Data to JObject.

Thanks!

[roji]

I'm not sure I follow what you're trying to do... Do you want to use your "proxy property" to somehow express queries over your real column in the database? Do you want the structure of the proxy property to somehow be nicer than your actual data? How would EF know what actual SQL query to generate?

A full, clearer code sample showing what you're trying could help here.

[StasPerekrestov]

Hello, @roji I've been thinking if it's possible to have something like the following

Let's assume we have a JSON of the following structure

{
    //...props
    "lienholder": {
        "Aba": "1234567890",
        "Name": "YORK COUNTY FCU",
        "Address": {
            "City": "SANFORD",
            "State": "ME",
            "ZipCode": "04073",
            "Address1": "1516 MAIN STREET",
            "Address2": "",
            "CountyCode": null
        },
        "LienholderId": 99894,
        "EncumbExpireDate": "2030-05-25",
        "IsLienElectronicTitle": true
    }
    //...some more props
}

and there is a EF entity

public class MyEntity 
{
    public JsonDocument Document { get; set; }
}

What if the team considers adding an extension method static T As<T>(this JsonDocument doc), which EF translates into proper PostgreSQL statements:

document #>'{lienholder,Address,ZipCode}'

var items = dbContext.MyEntities.Where(t => t.Document.GetProperty("lienholder").As<Lienholder>().Address.ZipCode == "04073");

//another sample
var items  = from t in dbContext.MyEntities
             let l = t.Document.GetProperty("lienholder").As<Lienholder>()
             select new { l.Aba, l.Name, l.Address.City };

I suppose this API could be very useful and save a lot of keystrokes.

Thank you.

[roji]

Coming back to this after a long time. I'm going to go ahead and close this, at least for now; with the way things currently work, you either have a stable schema and can fully model it via .NET types, or you have an unstable one and can model it via JSON "DOM" types (JsonDocument/JsonElement). It's not that I don't see a use for mixing the two, but this would be quite a complex thing to design, and I haven't seen many users actually requesting it.

In any case, this is discussion that's better done on the EF repo, since it's not specific to PostgreSQL in any way.