Projections don't work when changing JSON Settings

[phillip-haydon]

I have not verified this using Newtonsoft.Json but it's a bug that affects System.Text.Json.

Given the following:

            x.Serializer(new SystemTextJsonSerializer
            {
                EnumStorage = EnumStorage.AsString,
                Casing = Casing.Default
            });

If you have a document such as:

public class Player
{
    [JsonInclude]
    public int Id { get; private set; }

    public string Name { get; set; }

    public decimal Level { get; set; }

    public PlayerStatus Status { get; set; }
}

And you want to project this to say:

public class PlayerResponse
{
    public string Name { get; set; }

    public decimal Level { get; set; }
}

Querying like:

var skills = await conn.Query<Player>()
                       .Where(x => x.Status == PlayerStatus.Banned)
                       .Select(x => new PlayerResponse
                       {
                           Name = x.Name,
                           Skill = x.Skill
                       })
                       .ToListAsync();

This works... HOWEVER, if you (wipe the database, and start fresh) change the configuration:

            x.Serializer(new SystemTextJsonSerializer
            {
                EnumStorage = EnumStorage.AsString,
                Casing = Casing.CamelCase
            });

Everything works perfectly until you get to the project.

It issues a query like so:

select jsonb_build_object('Name', d.data ->> 'name', 'Level', CAST(d.data ->> 'level' as decimal)) as data
from game.mt_doc_player as d
where d.data ->> 'Status' = :p0;

This results in a collection of empty objects. Name is null and Level is 0.

I believe this is due to PropertyNameCaseInsensitive not being set to true in the serializer.

So there's 2 ways we can fix this I believe...

1. Change the serializer to be case insensitive. (This is my preferred approach)

2. Change the querying to use the same casing as the properties of the JSON. i.e

jsonb_build_object('name', d.data ->> 'name')

Name -> name since the data property is name.

[jeremydmiller]

@phillip-haydon

"

1. Change the serializer to be case insensitive. (This is my preferred approach)

2. Change the querying to use the same casing as the properties of the JSON. i.e
   "

Number 1 isn't feasible. Number 2 is already the behavior, so I'm not sure what you want to happen here. The default casing means that the Json is assumed to exactly match the property or field name.

If you're changing how the JSON is serialized, yeah, you're gonna break things and I don't know there's anything we can do about that. You can use the patching to migrate the data.

I'm converting this to a discussion for now.

[phillip-haydon]

So when you do a projection it uses the property names of the projection to generate the JSON document in postgresql to deserialize.

It seems with the System.Text.Json serializer it doesn’t like this. It’s like it’s expecting the properties to be lowercase but they are uppercase.

So if you create a new store like in the failing test, and the default is camelCase the jsonb document has lowercase properties but the projection has TitleCase.

It succeeds on a normal select but will fail on a projection.

Newtonsoft is case insensitive by default but system.text.json isn’t.

[jeremydmiller]

The postgresql operators are case sensitive though. We have to know how to translate a field/property name to the path within the JSON.

[phillip-haydon]

This isn’t about changing the serialization after the database is created. The issue occurs on a clean database that’s just created. I.e in the test.

[jeremydmiller]

Meh, okay. So STJ isn't correctly deserializing the selected JSON to the PlayerResponse type? Is that what's happening here?

[phillip-haydon]

Correct. Cos the serialization is setup as camelCase and the value is stored in the json doc as ‘name’ which is correct.

If you then fetch the doc it will deserialize correctly.

If you project it tho.

select jsonb_build_object('Name', d.data ->> 'name', 'Level', CAST(d.data ->> 'level' as decimal)) as data
from game.mt_doc_player as d
where d.data ->> 'Status' = :p0;

The object created is

{ “Name”:…

instead of

{ “name”:…

And so the projection fails to deserialize.

I donno why STJ is case sensitive, it’s annoying. But I didn’t want to enable it by default which is why I put it behind a property to enable.

(Sorry if formatting breaks I’m on my phone)

[jeremydmiller]

No clue about the STJ behavior. What you really seem to need is STJ to behave case insensitive on deserialization, but not serialization 'cause that would break the Linq support.

Or you use Newtonsoft that "just works"

[phillip-haydon]

What you really seem to need is STJ to behave case insensitive on deserialization

which is what the PR does by allowing that setting to be enabled. :D

The camelCase setting ensures saving is correct during serialization and persistence. And the WithPropertyNameCaseInsensitive ignores the casing during deserialization.

Or you use Newtonsoft that "just works"

But then I can’t break stuff cos it just works and that’s no fun haha!

[phillip-haydon]

I'm at my desk now so I can write a proper summary.

So to be 100% clear: The issue is solely in the configuration of System.Text.Json.

When using the setting:

PropertyNamingPolicy = JsonNamingPolicy.CamelCase

This will ensure when serialization occurs.

public string Name { get; set; } is serialized to { "name": ...}

This also means that deserialization looks for { "name": ...} to populate Name property.

So on a new clean database when the setting is set to camelCase everything works correctly in regards to CRUD operations.

The issue only occurs in projection, the reason is:

When a projection is run it generates a selection using the SQL Operators to build a new JSON document.

select jsonb_build_object('Name', d.data ->> 'name')) as data

(1stName is the Projection POCO property, the 2nd lowercase name is the DB Document POCO property.

When it takes the Name property from the POCO it maintains the casing. So we know that for querying Name becomes name but in the projection Name becomes Name.

So the way I see it is we have 2 options.

Option 1 (more risky, but... more correct?)

We change the generation of the SQL.

Because we use the configured casing to decide that the Document POCO changes Name to name (d.data ->> 'name') then we should be able to safely assume that the projection should also set Name to name.

This would mean no changes are required for the System.Text.Json configuration, but obviously affect everything else.

This feels more correct as the alignment of the SQL generation matches the configuration used for persistence and querying. But we are less likely to know if this has any other bugs pop out we aren't aware of.

Option 2

We change the serializer.

PropertyNameCaseInsensitive = true

https://docs.microsoft.com/en-us/dotnet/standard/serialization/system-text-json-character-casing

By default, deserialization looks for case-sensitive property name matches between JSON and the target object properties. To change that behavior, set JsonSerializerOptions.PropertyNameCaseInsensitive to true:

This setting only affects the deserialization from JSON to POCO. Thus we should be able to just enable it by default and be done with it. This wont have any affect on the serialization for persistance or the casing of the properties for persistance. It just means that it doesn't case what the casing in the JSON document is, it will match the property of the POCO you're trying to rehydrate.

Personaly I think it's really stupid that this is not the default setting. But enforcing case sensitivity probably shaved off 0.01ns from the performance so they could claim its crazy fast.

While I'm 100% sure this is absolutely perfectly safe to enable. I'm unsure if it's worth the risk. That's why my PR put it behind a property.

[phillip-haydon]

@jeremydmiller have you had any more thoughts on this?

I’ve been thinking and think we either do:

1. Just change the default setting to make it align with newtonsoft. (Set case insentive to true)

Or

2. I can update the documentation to mention this issue and how to work around SJTs lameness.