Filter and aggregation on JSON columns

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
MaxFerretti
Posts: 1
Joined: Fri 10 Dec 2021 09:13

Filter and aggregation on JSON columns

Post by MaxFerretti » 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:

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
Site Admin
Posts: 9516
Joined: Thu 14 Aug 2008 12:44

Re: Filter and aggregation on JSON columns

Post by Shalex » Mon 13 Dec 2021 10:23

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

Shalex
Site Admin
Posts: 9516
Joined: Thu 14 Aug 2008 12:44

Re: Filter and aggregation on JSON columns

Post by Shalex » Thu 27 Jan 2022 16:31

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.

Post Reply