Filter and aggregation on JSON columns
Posted: Fri 10 Dec 2021 09:21
Hello to all,
I have a Json column on a MySql database and I'm using dotConnect with EntityFramework 6.4.4 in a .NET application (4.6.2).
Inside the json column I have some products like this {name: string, weight: decimal}
I can easily filter on strings using
MySqlJsonFunctions.Extract(e.custom_props, "$.name") == "foo"
but I can't find a way to filter and aggregate on integer values.
For exampe I want to have something like that:
But obviously I can't because MySqlJsonFunctions only have methods that returns strings.
Adding Convert.ToInt32 also cannot work since the Sql generator cannot understand it.
Is there a way?
O am I supposed to threat it as strings, convert in memory and filter on the materialized objects?
Wouldn't it degrade performance very much?
Thank you!
I have a Json column on a MySql database and I'm using dotConnect with EntityFramework 6.4.4 in a .NET application (4.6.2).
Inside the json column I have some products like this {name: string, weight: decimal}
I can easily filter on strings using
MySqlJsonFunctions.Extract(e.custom_props, "$.name") == "foo"
but I can't find a way to filter and aggregate on integer values.
For exampe I want to have something like that:
Code: Select all
var query = db.test_events
.Where(e =>
e.Year = 2021 &&
MySqlJsonFunctions.Extract(e.custom_props, "$.name") == "foo" &&
MySqlJsonFunctions.Extract(e.custom_props, "$.weight") > 0 &&
.GroupBy(e => new { e.Year })
.Select(e =>
new
{
Year = e.Key.Year,
TotalWeight = e.Sum(p => MySqlJsonFunctions.ExtractUnquotedText(p.custom_props, "$.weight"))
}
).ToArray();Adding Convert.ToInt32 also cannot work since the Sql generator cannot understand it.
Is there a way?
O am I supposed to threat it as strings, convert in memory and filter on the materialized objects?
Wouldn't it degrade performance very much?
Thank you!