How do I write queries on json fields using equals, like and not equal?

Question

I have a column as a json type. Within the json data, there are several columns. We want to be able to query the data using equals, like and not equal operators. Any ideas on how to fix this?

Keep in mind, in this case, the column name is also dynamic and thus I cannot hardcode it in the where clause.

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
}

The MySQL query is malformed - SELECT * FROM server_logs WHERE log_type = 2 AND time_stamp >= '2023-03-10 15:55:14.304' AND time_stamp <= '2023-03-10 15:55:24.441' AND log->>$'severity' = 'High' ORDER BY time_stamp desc

The issue is the escaping - it should be log->>'$.severity'

If I try to construct the query string using fmt.Sprintf, it works but ofcourse we are exposed to SQL injection.

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)
            }

In the above case, the correct query string is generated.

Thank you for looking into this.

Answer 1

The -> or ->> operators do not accept expressions or parameters. They only accept JSON path as a string literal. So you must use JSON_UNQUOTE(JSON_EXTRACT(...)).

I would suggest formatting the JSON path in a Go expression before passing it as the parameter.

db = db.Where("json_unquote(json_extract(log, ?)) like ?",
  "$."+property, value)

An alternative is to use CONCAT() to format the JSON path, and pass the property alone as the parameter.

db = db.Where("json_unquote(json_extract(log, concat('$.', ?))) like ?",
  property, value)