Unable to specify naming policy when querying JSON fields

[giulianob]

I'm currently using JSON.NET and specifying a naming policy for snake case

            NpgsqlConnection.GlobalTypeMapper.UseJsonNet(settings: new JsonSerializerSettings
            {
                ContractResolver = new JsonContractResolver {
                    NamingStrategy = new SnakeCaseNamingStrategy()
                }
            });

I'm able to serialize/deserialize my JSON to/from a POCO just fine when doing simple read/writes to the db. However, when I'm trying to query a JSON field there's an issue. For example:

_context.Events.Where(evt => evt.Data.Snapshot.WorkspaceId == workspaceId);

The query generated is as follow:

SELECT ...
FROM event AS j
WHERE ((j.data#>>'{Snapshot,WorkspaceId}' = @__workspaceId_0) AND (@__workspaceId_0 IS NOT NULL))

The issue is that my JSON is in snake case so I would expect it to generate the following instead:

SELECT ...
FROM event AS j
WHERE ((j.data#>>'{snapshot,workspace_id}' = @__workspaceId_0) AND (@__workspaceId_0 IS NOT NULL))

Is there any configuration available to make this work?

[giulianob]

@roji I'm wondering if you can give any guidance whether this is possible right now I'm trying to integrate with an existing db that I have to follow its conventions 🙏

[markron]

I have the same problem using System.Text.Json too.
I configured the serialization options with the following code.

var mappingBuilder = new Npgsql.TypeMapping.NpgsqlTypeMappingBuilder
{
    PgTypeName = "jsonb",
    TypeHandlerFactory = new JsonbHandlerFactory(new JsonSerializerOptions
    {
        Converters = { /* ... */ },
        DictionaryKeyPolicy = new SnakeCaseJsonNamingPolicy(),
        PropertyNamingPolicy = new SnakeCaseJsonNamingPolicy()
    }),
    NpgsqlDbType = NpgsqlDbType.Jsonb,
    ClrTypes = new[] { typeof(JsonDocument) }
};

Npgsql.NpgsqlConnection.GlobalTypeMapper.RemoveMapping("jsonb");
Npgsql.NpgsqlConnection.GlobalTypeMapper.AddMapping(mappingBuilder.Build());

As noted by the author of the issue, everything works fine while serializing to and deserializing from JSONB columns.
JSON fields referred in LINQ expressions, however, are not converted with the same policies used for serialization (e.g. a.name->>'SomeProperty' instead of a.name->>'some_property' )

I know the limitation is probably already known, just wanted to note that it doesn't seem to be specific of the JSON.NET integration.

[roji]

@giulianob I think you may be mixing JSON.NET and System.Text.Json. At the lower ADO level, Npgsql has a JSON.NET plugin (Npgsql.Json.NET), and 4.1 also added support for the new System.Text.Json (without any plugin). At the higher EF Core level, only System.Text.Json is supported for now, and there's unfortunately no support yet for configuring the naming policy (or any other serialization option). #1107 already tracks that, and I definitely see it as an important issue that we should do soon.

@markron's answer can help for serialization/loading, but as they noted it doesn't take care of generating queries over JSON columns in the database (the solution is very similar to this one.

Am closing this as a duplicate of #1107.