gorm+mysql查询/修改json列相关操作汇总

目录

具体操作

1，查询JSON段落指定key的值是否有等于value的 或 指定key=value的数据记录

查询ids列中k1=val1的记录

var demo1 []Demo
err = conn.Model(&Demo{}).Where("ids->>'$.k1' = ?", "val1").Find(&demo1).Error 

或

查询books列中price=19.1的记录

var demo1 []Demo
err = conn.Model(&Demo{}).Where("JSON_EXTRACT(`books`,'$.price')=?", 19.1).Find(&demo1).Error

2，查询JSON段落中price>19的记录

var demo1 []Demo
err = conn.Model(&Demo{}).Where("books->'$.price' > 19 ").Find(&demo1).Error

等价于：

SELECT * FROM `demos` WHERE  books->>'$.price' >= 19;

3，查询JSON段中key为k0的记录

var demo1 []Demo
err = conn.Model(&Demo{}).Where("JSON_EXTRACT(ids, '$.k0')").Find(&demo1).Error

4、JSON段落中提取指定键值对到指定结构

var demo2 []Book
err = conn.Model(&Demo{}).Select("books->>'$.name' AS name").Find(&demo2).Error
// 注意：在非数组的JSON中可正常获取，若是数组型JSON则拿不到数据都是零值。

提取多个键值对（多个字段）：

err = conn.Model(&Demo{}).Select("books->>'$.name' AS name", "books->>'$.price' AS price").Find(&demo2).Error

5，查询JSON数组是否包含某元素

查询ids列的JSON数组中包含a0的记录

var demo1 []Demo
err = conn.Model(&Demo{}).Where("JSON_CONTAINS(ids, JSON_ARRAY(?))", "a0").Find(&demo1).Error

等价于

SELECT * FROM `demos` WHERE JSON_CONTAINS(`ids`, JSON_ARRAY('a0') ) AND `demos`.`deleted_at` IS null;

6，修改JSON段中指定key的值

将id=1的ids列中key为k0的值修改为v00

err = conn.Model(&Demo{}).Where("id=?", 1).Update("ids", gorm.Expr("JSON_REPLACE(ids, '$.k0', ?)", "v00")).Error // JSON_SET也可

值得注意的是，这种修改操作不能作用于JSON数组中元素的修改，JSON数组中元素的修改需要用到'$[0].k0'，即表示数组第一个位置的元素的k0的值。

相关结构体及基础数据

type Demo struct {
    entity.Model
    IDs   string `json:"ids"  gorm:"column:ids; type:JSON;"`
    Books string `json:"books"  gorm:"column:books; type:JSON;"`
}

type Book struct {
    Name  string  `json:"name" gorm:"column:name;type:varchar(20); not null;"`
    Price float64 `json:"price" gorm:"column:price;type:decimal(10,2); not null; default:0.00;"`
}

    demos := []Demo{
        Demo{
            IDs:   "{\"k0\":\"val0\"}",
            Books: "{\"name\":\"西游记\",\"price\": 19.0}",
        },
        Demo{
            IDs:   "{\"k1\":\"val1\",\"k0\":\"val2\"}",
            Books: "{\"name\":\"三国演义\",\"price\": 19.1}",
        },
        Demo{
            IDs:   `["a0","a1","a2"]`,
            Books: "[{\"name\":\"西游记\",\"price\": 19.0},{\"name\":\"三国演义\",\"price\": 19.1}]",
        },
    }

    err = conn.Create(demos).Error