首页 > SqlSugar

MySQL 8 Json 字段怎么查询返回

SqlSugar 老数据

 8  2677

麦稻茶 发布于2021/8/30

悬赏：5 飞吻

使用JSON字段查询的时候，报错提示要先Mapper，这个Mapper该怎么写，请教一下。

测试代码如下：

//json内容查询 MySQL 8.0.26 .net framework 4.8

//SqlSugar 5.0.3.8

public class Order

{

[SugarColumn(IsIdentity = true, IsPrimaryKey = true)]

public int Id { get; set; }

public int Value { get; set; }

[SugarColumn(IsJson = true, ColumnDataType = "Json")]

public SubData SubData { get; set; }

}

public class SubData

{

public int Value1 { get; set; }

public int Value2 { get; set; }

public SubData(int v1, int v2)

{

Value1 = v1;

Value2 = v2;

}

private void button1_Click(object sender, EventArgs e)

{

var db = NewDB();

//插入模拟数据

//List<Order> orders = new List<Order>()

//{

// new Order(){Value=1, SubData = new SubData(2,3) },

// new Order(){Value=2, SubData = new SubData(2,5) },

// new Order(){Value=3, SubData = new SubData(3,5) },

//};

//db.Insertable(orders).ExecuteCommand();

//下面这一句报错：当前表达式x.SubData.Value1必须在Mapper之后使用

var items = db.Queryable<Order>().Where(x => x.SubData.Value1 > 2).ToList();

}

热忱回答（8）

fate stay night VIP0
2021/8/30

JSON对象肯定是不能直接点的，需要写扩展函数实现
https://www.donet5.com/Home/Doc?typeId=1225

0 回复
fate stay night VIP0
2021/8/30

只能作一般的CURD ，你要用JSON的查询特性，不同数据库用法也一样

0 回复

麦稻茶 VIP0

2021/8/31

扩展函数太难了，能力不足啊，看不懂。研究了一下源码，还是没太明白是怎么解析表达树的。

直接在网上弄了个表达树解析的代码，稍微修改一下，测试了一下可以用。

这里提供一下如何实现，希望能帮助到大家。大神可自行使用 SqlSugar里面的表达树解析工具进行处理。

代码未经优化，能力有限，谅解。

方法原理：

1、编写IQueryable的扩展方法：WhereJson(...)

2、在扩展方法中解析lambda表达树，生成SQL字符串。

3、在扩展方法中，将生成的SQL通过 Queryable.Where(whereString)加进去，返回。

最终使用效果可以达到：

//提问的例子有结构定义
 var items = db.Queryable<Order>().WhereJson(x => x.SubData.Value1 > 2 && x.SubData.Value2 < 6).ToList();

初步步骤：

1、新建扩展方法：

    public static class SqlSugarExtension
    {
        public static ISugarQueryable<T> WhereJson<T>(this ISugarQueryable<T> query, Expression<Func<T, bool>> expression)
        {      
            var translator = new QueryTranslator();
            string whereClause = translator.Translate(expression);
            query = query.Where(whereClause);
            return query;
        } 
    }

2、实现 QueryTranslator (重点代码自行搜索：关键字：MemberAccess)

  public class QueryTranslator : ExpressionVisitor
    {
        private StringBuilder sb;
        private string _orderBy = string.Empty;
        private int? _skip = null;
        private int? _take = null;
        private string _whereClause = string.Empty;
        public int? Skip
        {
            get
            {
                return _skip;
            }
        }
        public int? Take
        {
            get
            {
                return _take;
            }
        }
        public string OrderBy
        {
            get
            {
                return _orderBy;
            }
        }
        public string WhereClause
        {
            get
            {
                return _whereClause;
            }
        }
        public QueryTranslator()
        {
        }
        public string Translate(Expression expression)
        {
            this.sb = new StringBuilder();
            this.Visit(expression);
            _whereClause = this.sb.ToString();
            return _whereClause;
        }
        private static Expression StripQuotes(Expression e)
        {
            while (e.NodeType == ExpressionType.Quote)
            {
                e = ((UnaryExpression)e).Operand;
            }
            return e;
        }
        protected override Expression VisitMethodCall(MethodCallExpression m)
        {
            if (m.Method.DeclaringType == typeof(Queryable) && m.Method.Name == "Where")
            {
                this.Visit(m.Arguments[0]);
                LambdaExpression lambda = (LambdaExpression)StripQuotes(m.Arguments[1]);
                this.Visit(lambda.Body);
                return m;
            }
            else if (m.Method.Name == "Take")
            {
                if (this.ParseTakeExpression(m))
                {
                    Expression nextExpression = m.Arguments[0];
                    return this.Visit(nextExpression);
                }
            }
            else if (m.Method.Name == "Skip")
            {
                if (this.ParseSkipExpression(m))
                {
                    Expression nextExpression = m.Arguments[0];
                    return this.Visit(nextExpression);
                }
            }
            else if (m.Method.Name == "OrderBy")
            {
                if (this.ParseOrderByExpression(m, "ASC"))
                {
                    Expression nextExpression = m.Arguments[0];
                    return this.Visit(nextExpression);
                }
            }
            else if (m.Method.Name == "OrderByDescending")
            {
                if (this.ParseOrderByExpression(m, "DESC"))
                {
                    Expression nextExpression = m.Arguments[0];
                    return this.Visit(nextExpression);
                }
            }
            throw new NotSupportedException(string.Format("The method '{0}' is not supported", m.Method.Name));
        }
        protected override Expression VisitUnary(UnaryExpression u)
        {
            switch (u.NodeType)
            {
                case ExpressionType.Not:
                    sb.Append(" NOT ");
                    this.Visit(u.Operand);
                    break;
                case ExpressionType.Convert:
                    this.Visit(u.Operand);
                    break;
                default:
                    throw new NotSupportedException(string.Format("The unary operator '{0}' is not supported", u.NodeType));
            }
            return u;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        protected override Expression VisitBinary(BinaryExpression b)
        {
            sb.Append("(");
            this.Visit(b.Left);
            switch (b.NodeType)
            {
                case ExpressionType.And:
                    sb.Append(" AND ");
                    break;
                case ExpressionType.AndAlso:
                    sb.Append(" AND ");
                    break;
                case ExpressionType.Or:
                    sb.Append(" OR ");
                    break;
                case ExpressionType.OrElse:
                    sb.Append(" OR ");
                    break;
                case ExpressionType.Equal:
                    if (IsNullConstant(b.Right))
                    {
                        sb.Append(" IS ");
                    }
                    else
                    {
                        sb.Append(" = ");
                    }
                    break;
                case ExpressionType.NotEqual:
                    if (IsNullConstant(b.Right))
                    {
                        sb.Append(" IS NOT ");
                    }
                    else
                    {
                        sb.Append(" <> ");
                    }
                    break;
                case ExpressionType.LessThan:
                    sb.Append(" < ");
                    break;
                case ExpressionType.LessThanOrEqual:
                    sb.Append(" <= ");
                    break;
                case ExpressionType.GreaterThan:
                    sb.Append(" > ");
                    break;
                case ExpressionType.GreaterThanOrEqual:
                    sb.Append(" >= ");
                    break;
                default:
                    throw new NotSupportedException(string.Format("The binary operator '{0}' is not supported", b.NodeType));
            }
            this.Visit(b.Right);
            sb.Append(")");
            return b;
        }
        protected override Expression VisitConstant(ConstantExpression c)
        {
            IQueryable q = c.Value as IQueryable;
            if (q == null && c.Value == null)
            {
                sb.Append("NULL");
            }
            else if (q == null)
            {
                switch (Type.GetTypeCode(c.Value.GetType()))
                {
                    case TypeCode.Boolean:
                        sb.Append(((bool)c.Value) ? 1 : 0);
                        break;
                    case TypeCode.String:
                        sb.Append("'");
                        sb.Append(c.Value);
                        sb.Append("'");
                        break;
                    case TypeCode.DateTime:
                        sb.Append("'");
                        sb.Append(c.Value);
                        sb.Append("'");
                        break;
                    case TypeCode.Object:
                        throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", c.Value));
                    default:
                        sb.Append(c.Value);
                        break;
                }
            }
            return c;
        }
        protected override Expression VisitMember(MemberExpression m)
        {
            if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter)
            {
                sb.Append(m.Member.Name);
                return m;
            }
            if (m.Expression != null && m.Expression.NodeType == ExpressionType.MemberAccess)
            {
                var nodeType = ExpressionType.MemberAccess;
                var expMain = m;
                var stack = new Stack<string>();
                while (nodeType == ExpressionType.MemberAccess)
                {
                    stack.Push(expMain.Member.Name);
                    expMain = expMain.Expression as MemberExpression;
                    if (expMain == null) break;
                    nodeType = expMain.NodeType;
                }
                sb.Append(stack.Pop());
                sb.Append("->'$");                
                while (stack.Count > 0)
                {                    
                  sb.Append($".{stack.Pop()}");
                }
                sb.Append("'")
                return m;
            }
            throw new NotSupportedException(string.Format("The member '{0}' is not supported", m.Member.Name));
        }
        protected bool IsNullConstant(Expression exp)
        {
            return (exp.NodeType == ExpressionType.Constant && ((ConstantExpression)exp).Value == null);
        }
        private bool ParseOrderByExpression(MethodCallExpression expression, string order)
        {
            UnaryExpression unary = (UnaryExpression)expression.Arguments[1];
            LambdaExpression lambdaExpression = (LambdaExpression)unary.Operand;
            lambdaExpression = (LambdaExpression)Evaluator.PartialEval(lambdaExpression);
            MemberExpression body = lambdaExpression.Body as MemberExpression;
            if (body != null)
            {
                if (string.IsNullOrEmpty(_orderBy))
                {
                    _orderBy = string.Format("{0} {1}", body.Member.Name, order);
                }
                else
                {
                    _orderBy = string.Format("{0}, {1} {2}", _orderBy, body.Member.Name, order);
                }
                return true;
            }
            return false;
        }
        private bool ParseTakeExpression(MethodCallExpression expression)
        {
            ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];
            int size;
            if (int.TryParse(sizeExpression.Value.ToString(), out size))
            {
                _take = size;
                return true;
            }
            return false;
        }
        private bool ParseSkipExpression(MethodCallExpression expression)
        {
            ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];
            int size;
            if (int.TryParse(sizeExpression.Value.ToString(), out size))
            {
                _skip = size;
                return true;
            }
            return false;
        }
    }

3、实现Evaluator

    public static class Evaluator
    {
        /// <summary>
        /// Performs evaluation & replacement of independent sub-trees
        /// </summary>
        /// <param name="expression">The root of the expression tree.</param>
        /// <param name="fnCanBeEvaluated">A function that decides whether a given expression node can be part of the local function.</param>
        /// <returns>A new tree with sub-trees evaluated and replaced.</returns>
        public static Expression PartialEval(Expression expression, Func<Expression, bool> fnCanBeEvaluated)
        {
            return new SubtreeEvaluator(new Nominator(fnCanBeEvaluated).Nominate(expression)).Eval(expression);
        }
        /// <summary>
        /// Performs evaluation & replacement of independent sub-trees
        /// </summary>
        /// <param name="expression">The root of the expression tree.</param>
        /// <returns>A new tree with sub-trees evaluated and replaced.</returns>
        public static Expression PartialEval(Expression expression)
        {
            return PartialEval(expression, Evaluator.CanBeEvaluatedLocally);
        }
        private static bool CanBeEvaluatedLocally(Expression expression)
        {
            return expression.NodeType != ExpressionType.Parameter;
        }
        /// <summary>
        /// Evaluates & replaces sub-trees when first candidate is reached (top-down)
        /// </summary>
        class SubtreeEvaluator : ExpressionVisitor
        {
            HashSet<Expression> candidates;
            internal SubtreeEvaluator(HashSet<Expression> candidates)
            {
                this.candidates = candidates;
            }
            internal Expression Eval(Expression exp)
            {
                return this.Visit(exp);
            }
            public override Expression Visit(Expression exp)
            {
                if (exp == null)
                {
                    return null;
                }
                if (this.candidates.Contains(exp))
                {
                    return this.Evaluate(exp);
                }
                return base.Visit(exp);
            }
            private Expression Evaluate(Expression e)
            {
                if (e.NodeType == ExpressionType.Constant)
                {
                    return e;
                }
                LambdaExpression lambda = Expression.Lambda(e);
                Delegate fn = lambda.Compile();
                return Expression.Constant(fn.DynamicInvoke(null), e.Type);
            }
        }
        /// <summary>
        /// Performs bottom-up analysis to determine which nodes can possibly
        /// be part of an evaluated sub-tree.
        /// </summary>
        class Nominator : ExpressionVisitor
        {
            Func<Expression, bool> fnCanBeEvaluated;
            HashSet<Expression> candidates;
            bool cannotBeEvaluated;
            internal Nominator(Func<Expression, bool> fnCanBeEvaluated)
            {
                this.fnCanBeEvaluated = fnCanBeEvaluated;
            }
            internal HashSet<Expression> Nominate(Expression expression)
            {
                this.candidates = new HashSet<Expression>();
                this.Visit(expression);
                return this.candidates;
            }
            public override Expression Visit(Expression expression)
            {
                if (expression != null)
                {
                    bool saveCannotBeEvaluated = this.cannotBeEvaluated;
                    this.cannotBeEvaluated = false;
                    base.Visit(expression);
                    if (!this.cannotBeEvaluated)
                    {
                        if (this.fnCanBeEvaluated(expression))
                        {
                            this.candidates.Add(expression);
                        }
                        else
                        {
                            this.cannotBeEvaluated = true;
                        }
                    }
                    this.cannotBeEvaluated |= saveCannotBeEvaluated;
                }
                return expression;
            }
        }
    }

0 回复

麦稻茶 VIP0
2021/8/31

大部分功能，在SqlSugar中的【SqlSugar.ExpressionTool】都有实现，只要解析：NodeType为MemberAccess就可以了。
但没时间细看和理解，所以没优化，大神自行优化，减少重复代码。

0 回复
fate stay night VIP0
2021/8/31

现成的例子： https://www.donet5.com/Home/Doc?typeId=1225 直接拿去改

0 回复
麦稻茶 VIP0
2021/8/31

@fate stay night：这个看不太懂，不会改。
试改了一下，发现传入参数写：x.SubData.Value1，还是会报mapper的错误，除非参数传字符串
不知道怎么改才能让他支持 lambda表达式

0 回复
fate stay night VIP0
2021/8/31

就算是扩展函数也只是是函数（ x.字段, " xxx" ) 这么实现这个字符串和数据库方式一样

0 回复
fate stay night VIP0
2021/8/31

如果你想 X.字段.JSON属性这么用肯定不行

0 回复

首页 > SqlSugar

MySQL 8 Json 字段怎么查询 返回

热忱回答（8）

MySQL 8 Json 字段怎么查询返回