Filter and aggregation on JSON columns

[MaxFerretti]

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:

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();

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!

[Shalex]

Thank you for your request. We will investigate the question and notify you about the result.

[Shalex]

The new methods ExtractAsByte(), ExtractAsSByte(), ExtractAsInt16(), ExtractAsInt32(), ExtractAsInt64(), ExtractAsSingle(), ExtractAsDouble(), ExtractAsDecimal(), ExtractAsDateTime() are added to the MySqlJsonFunctions class for working with the JSON data type in LINQ to Entities queries.

Refer to viewtopic.php?f=2&t=53186.