Json for special key

[wuyu8512]

Hello, I have some data like

{
   "318":{
      "cid":8,
      "xPath":"//*[@id=\"app\"]/div[1]/div[2]/div[1]/div[1]/div[2]/div[1]/p[411]"
   },
   "329":{
      "cid":7,
      "xPath":""
   }
}

var readPostion = await _context.UserData.Where(x => x.UserId == userId)
    .Select(x => x.ReadPosition[bid.ToString()])
    .FirstOrDefaultAsync();

ReadPosition is JObject, bid is int

They generated sql statements like this

SELECT JSON_EXTRACT(`u`.`read_position`, '$.318')
FROM `user_data` AS `u`
WHERE `u`.`user_id` = 2
LIMIT 1

and throw an error Invalid JSON path expression. The error is around character position 5.

The correct one should be like this

SELECT JSON_EXTRACT(`u`.`read_position`, '$."318"')
FROM `user_data` AS `u`
WHERE `u`.`user_id` = 2
LIMIT 1

Is this a bug, or am I missing something?

[mguinness]

Please provide which version of Pomelo.EntityFrameworkCore.MySql you're using. It should have been stated that you configured Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft for JSON support.

I suspect that the ReadPosition argument doesn't support expressions. Try defining a variable beforehand to cast bid to a string and use that instead.

[wuyu8512]

Please provide which version of Pomelo.EntityFrameworkCore.MySql you're using. It should have been stated that you configured Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft for JSON support.

I suspect that the ReadPosition argument doesn't support expressions. Try defining a variable beforehand to cast bid to a string and use that instead.

my Pomelo.EntityFrameworkCore.MySql is 6.0.0

var userId = _user.GetUserID();
var bidString = bid.ToString();
var readPostion = await _context.UserData.Where(x => x.UserId == userId)
    .Select(x => x.ReadPosition[bidString])
    .FirstOrDefaultAsync();

the result is same

my config

services.AddDbContext<BookShelfContext>(options =>
{
    options.UseMySql(connectionString, serverVersion,
        builder =>
        {
            builder.EnableRetryOnFailure();
            builder.UseNewtonsoftJson(MySqlCommonJsonChangeTrackingOptions.FullHierarchyOptimizedFast);
        });
    if (Env.IsDevelopment())
    {
        options.EnableSensitiveDataLogging();
        options.EnableDetailedErrors();
        //options.LogTo(Console.WriteLine, LogLevel.Debug);
    }
});

[mguinness]

This is a long shot, but you could try x.ReadPosition.GetValue(bidString) or x.ReadPosition.Root[bidString] also.

Below is the code for the expression visitor which will need to be debugged. It could be just adding quotes to append.

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/00bfeecd11eeda988d5d934706ff6af6c0a7c13b/src/EFCore.MySql/Query/ExpressionVisitors/Internal/MySqlQuerySqlGenerator.cs#L76-L154

[wuyu8512]

This is a long shot, but you could try x.ReadPosition.GetValue(bidString) or x.ReadPosition.Root[bidString] also.

It works good

[wuyu8512]

This is a long shot, but you could try x.ReadPosition.GetValue(bidString) or x.ReadPosition.Root[bidString] also.

hi，x.ReadPosition.Root[bidString] has same problem

x.ReadPosition.GetValue[bidString] is ok , but he generated such sql statement

SELECT `u`.`read_position`
FROM `user` AS `u`
WHERE `u`.`id` = @__userId_0
LIMIT 1

Cannot query in sql statement