MySQL 判断 JSON 数组是否包含某元素

文章目录

• 1.问题描述
• 2.使用 JSON_CONTAINS 函数
• 3.使用 gorm.io/datatypes
• 4.JSON 数组包含多个元素的任意一个
• 参考文献

在 MySQL 中，并没有内置的数组数据类型。但是，MySQL 提供了一些可以实现类似数组功能的机制，如使用字符串来存储由逗号分隔的值，或使用 JSON 类型来存储数组数据等。

假设您正在使用 GORM 和 MySQL 数据库，并且您的数据表中有一个名为 data 的 JSON 类型的列，其中存储了一个字符串数组，您想要查询该数组是否包含某个元素。

import ("gorm.io/datatypes")type MyModel struct {    ID   uint    Data datatypes.JSON `gorm:"column:data"`}

MySQL JSON_CONTAINS 函数可用于判断 JSON 数组中是否包含某个元素。

JSON_CONTAINS 的格式如下：

JSON_CONTAINS(target, candidate[, path])

target 要搜索的 JSON 数据。

candidate 要查找的 JSON 值或对象。

path (可选) 指定一个 JSON 路径表达式，用于在目标 JSON 数据中定位要搜索的子对象。

包含返回 1， 不包含返回 0。如果任何参数为 NULL，或 path 参数没有标识目标文档的部分，则返回 NULL。如果 target 或 candidate 不是有效的 JSON 文档，或者 path 不是有效的路径表达式或包含*或**通配符，则发生错误。

我们可以使用原生 SQL 作为 GORM 的内联条件来判断 JSON 数组中是否包含某值。

var rows []MyModelDB.Where(fmt.Sprintf(`JSON_CONTAINS(data,'"%v"')`, YOUR_STR_VALUE)).Find(&rows)

因为 JSON 字符串数组中的元素是被双引号包裹的，所以指定字符串时，需要指定双引号。此时 SQL 字符串包含双引号，可以使用单引号表示包含双引号的字符串。

如果需要指定多个值，可以使用 JSON_ARRAY 函数将多个值创建为 JSON 数组。

如果指定多个值，表示要同时包含多个值条件才为 true。

DB.Where(fmt.Sprintf(`JSON_CONTAINS(data, JSON_ARRAY("%v")`, YOUR_STR_VALUE)).Find(&rows)

JSON_CONTAINS 函数还可以判断 JSON 对象中某个字段的值是否为指定的值，具体用法可参见 MySQL 官网的介绍。

gorm.io/datatypes 是 GORM v2 版本中引入的一个包，提供了一些数据库特定的数据类型，例如 JSON、HSTORE、ARRAY、UUID 等。

datatypes 支持对 JSON 数组的包含查询。

var rows []MyModelDB.Where(datatypes.JSONArrayQuery("data").Contains("YOUR_STR_VALUE")).Find(&rows)

如果给定多个元素，JSON 数组中只要包含其中任意一个元素，则认为是 true，该如何实现呢？

MySQL 5.7 和 8.0 均未包含一个类似 JSON_CONTAINS 的函数 JSON_CONTAINS_ANY 来实现我们想要的效果。

在 MySQL 5.7 中，我们可以使用 OR 运算符实现。

SELECT * FROM   table_nameWHERE  JSON_CONTAINS(json_array_column, value1) OR JSON_CONTAINS(json_array_column, value2)

如果使用 gorm.io/datatypes 实现的话，类似于下面的实现。

db := DB.Model(&MyModel{})for i, v := range values{if i == 0 {db.Where(datatypes.JSONArrayQuery("json_array_column").Contains(v))} else {db.Or(datatypes.JSONArrayQuery("json_array_column").Contains(v))}}

从 MySQL 8.0 开始，可以使用 JSON_OVERLAPS 函数。

SELECT * FROM   table_nameWHERE  JSON_OVERLAPS(json_array_column, JSON_ARRAY(val[, val] ...]))

---

OpenAI ChatGPT
12.18.3 Functions That Search JSON Values
mysql，查询json数组字段中包含某个元素的sql语句 - CSDN博客
MySQL Filter JSON_CONTAINS Any value from Array - stackoverflow.com

来源地址：https://blog.csdn.net/K346K346/article/details/128863722