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.

Pomelo.EntityFrameworkCore.MySql/src/EFCore.MySql/Query/ExpressionVisitors/Internal/MySqlQuerySqlGenerator.cs

Lines 76 to 154 in 00bfeec

	protected virtual Expression VisitJsonPathTraversal(MySqlJsonTraversalExpression expression)
	{
	// If the path contains parameters, then the -> and ->> aliases are not supported by MySQL, because
	// we need to concatenate the path and the parameters.
	// We will use JSON_EXTRACT (and JSON_UNQUOTE if needed) only in this case, because the aliases
	// are much more readable.
	var isSimplePath = expression.Path.All(
	l => l is SqlConstantExpression ||
	l is MySqlJsonArrayIndexExpression e && e.Expression is SqlConstantExpression);
	if (expression.ReturnsText)
	{
	Sql.Append("JSON_UNQUOTE(");
	}
	if (expression.Path.Count > 0)
	{
	Sql.Append("JSON_EXTRACT(");
	}
	Visit(expression.Expression);
	if (expression.Path.Count > 0)
	{
	Sql.Append(", ");
	if (!isSimplePath)
	{
	Sql.Append("CONCAT(");
	}
	Sql.Append("'$");
	foreach (var location in expression.Path)
	{
	if (location is MySqlJsonArrayIndexExpression arrayIndexExpression)
	{
	var isConstantExpression = arrayIndexExpression.Expression is SqlConstantExpression;
	Sql.Append("[");
	if (!isConstantExpression)
	{
	Sql.Append("', ");
	}
	Visit(arrayIndexExpression.Expression);
	if (!isConstantExpression)
	{
	Sql.Append(", '");
	}
	Sql.Append("]");
	}
	else
	{
	Sql.Append(".");
	Visit(location);
	}
	}
	Sql.Append("'");
	if (!isSimplePath)
	{
	Sql.Append(")");
	}
	Sql.Append(")");
	}
	if (expression.ReturnsText)
	{
	Sql.Append(")");
	}
	return expression;
	}

[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