Mysql 如何使用等于、喜欢和不等于在 json 字段上编写查询

我有一个json类型的专栏.在json数据中，有几列.我们希望能够使用等于、LIKE和NOT EQUAL操作符查询数据.有什么办法解决这个问题吗？

请记住，在本例中，列名也是动态的，因此我不能在WHERE子句中对其进行硬编码.

func applyServerLogsQuery(db *gorm.DB, logType api.LogDestination_LogType, req *logs.GetLogsRequest) (*gorm.DB, error) {
    // set log type
    db = db.Where("log_type = ?", logType)

    // check for time
    if !req.StartTime.IsZero() {
        db = db.Where("time_stamp >= ?", req.StartTime)
    }

    if !req.EndTime.IsZero() {
        db = db.Where("time_stamp <= ?", req.EndTime)
    }

    // process filters
    for _, filter := range req.Filters {
        if len(filter.Values) == 0 {
            continue
        }

        property := filter.PropertyInfo.Name
        value := filter.Values[0]

        switch filter.FilterType {
        case logs.FilterTypeRegex:
            db = db.Where("log->>$? like ?", property, value)

        case logs.FilterTypeShould:
            db = db.Where("log->>$? = ?", property, value)

        case logs.FilterTypeExclude:
            db = db.Where("log->>$? != ?", property, value)
        }

        /*
            prefix := fmt.Sprintf("log->>'$.%s'", filter.PropertyInfo.Name)
            value := filter.Values[0]
            queryString := fmt.Sprintf("log->>'$.%s' like '%s'", filter.PropertyInfo.Name, value)

            log.Printf("Prefix %s value %s query %s", prefix, value, queryString)

            switch filter.FilterType {
            case logs.FilterTypeRegex:
                db = db.Where(queryString)
            }
        */
    }

    // handle order by time
    orderStr := "time_stamp"
    if req.OrderDesc {
        orderStr = fmt.Sprintf("%s desc", orderStr)
    } else {
        orderStr = fmt.Sprintf("%s asc", orderStr)
    }
    db = db.Order(orderStr)

    // handle limit
    db = db.Limit(int(req.Limit))

    return db, nil
}

MySQL查询格式错误- SELECT*FORserver_logs WHERE LOG_TYPE=2 AND TIME_STAMP&gt；=‘2023-03-10 15:55:14.304’AND TIME_STAMP&lt；=‘2023-03-10 15:55:24.441’AND LOG-&>；&gt；$‘Severity’=‘High’ORDER BY TIME_STAMP Desc

问题在于转义--它应该是对数的--&gt；&gt；‘$.Severity’

如果我try 使用fmt.Sprint tf构造查询字符串，它会起作用，但当然，我们会受到SQL注入的影响.

prefix := fmt.Sprintf("log->>'$.%s'", filter.PropertyInfo.Name)
            value := filter.Values[0]
            queryString := fmt.Sprintf("log->>'$.%s' like '%s'", filter.PropertyInfo.Name, value)

            log.Printf("Prefix %s value %s query %s", prefix, value, queryString)

            switch filter.FilterType {
            case logs.FilterTypeRegex:
                db = db.Where(queryString)
            }

在上面的例子中，生成了正确的查询字符串.

谢谢你调查这件事.