Question: Do you support JSONB for querying fields

[AntyaDev]

Do you support JSONB for querying fields?

[rhuijben]

Looks like this was easier to implement than I expected. Initial support landed in PR #36.

sql.Query<Entity>(x => x.JsonColumn.JsonExtract<int>("age") > 12)

will work on columns of types JsonNode and JsonObject to extract an age element. (These column types already mostly worked, but are now properly supported)

The path must (currently) be a constant string, but can be something like "home.addresses[12].street".

This will then be converted to the db required syntax for all currently supported providers.

It will probably be possible to lift some of the current restrictions later on.

Perhaps we can also later add a sql.Query<Entity>(x => x.JsonColumn.JsonExtract<JsonType>(t => t.Age) > 12) to make this completely generated.

[rhuijben]

And that was even easier now..

That last format would require sql.Query<Entity>(x => x.JsonColumn.JsonExtract<JsonType, int>(t => t.Age) > 12)

but sql.Query<Entity>(x => x.JsonColumn.JsonExtract((JsonType t) => t.Age) > 12) would accomplish the same thing with less typing.

This support on json columns and jsonb columns (for PostgreSql) is now available on the main branch, and will be released early next month.

[AntyaDev]

Thanks @rhuijben
It's amazing news!

[rhuijben]

Code for detecting the generated columns is now in the latest nuget release. Should be visibile in the next half hour.

You can now use JsonNode or JsonObject for columns and use that extract trick or you can use the new type DbJsonValue and use a wrapper. For PostgreSql this works for json and jsonb columns. For other database providers on their prefered json field types. (mostly something like varchar/text)

Depends on how you build your objects what would be easier to use. I assume the DbJsonValue api is not strictly stable yet, but it makes it easy to use simple json types in your code with standard serialize/deserialize and some easy queries.

    [Table(nameof(JsonTestClass))]
    public record class JsonTestClassValues
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public DbJsonValue<JsonBTestClass> JsonNode { get; set; }
        public DbJsonValue<JsonBTestClass>? Object { get; set; }
        public DbJsonValue<string[]> Array { get; set; }
    }

    [TestMethod]
    public async Task CreateUpdateAndFetchJsonValue()
    {
        using var sql = await CreateOpenConnectionAsync();

        // <setup-code />

        await sql.InsertAsync(new JsonTestClassValues
        {
            Id = 1,
            Name = "Test",
            JsonNode = new JsonBTestClass { Id = 5 },
            Object = new JsonBTestClass { },
            Array = new string[] { "a", "b" }
        });

        // Syntax as above... via ExtractValue
        r = await sql.QueryAsync<JsonTestClassValues>(v => v.JsonNode.ExtractValue(x => x.Id) == 5);
        Assert.HasCount(1, r);

        // Or by detecting the special column type, the ExtractValue case is automatically detected
        r = await sql.QueryAsync<JsonTestClassValues>(v => v.JsonNode.Value.Id == 5);
        Assert.HasCount(1, r);

        // accessing arrays and nested types via the query syntax is supported too.
    }

You can provide basic serializer settings with the onetime startup of RepoDB. (e.g. how you want your casing rules to apply in json)