MySQL嵌套JSON数组条件查询及Spring JPA简化实现问询

需求说明 #

从MySQL的JSON字段中检索任务数据，仅展示给assignedTo数组中任意人员或reviewedBy对象对应的人员，匹配条件为mailId。

目标JSON结构 #

[
    {
        "taskName": null,
        "endDate": null,
        "assignedTo": [
            {
                "mail": "xxxxxxx.com",
                "employeeName": "xyz xyz",
                "departmentName": "ttttt",
                "region": "aaaaa"
            },
            {
                "mail": "zzzz.com",
                "employeeName": "kklkk",
                "departmentName": "tyeeee",
                "region": "iiiii"
            }
        ],
        "assignedBy": null,
        "labels": null,
        "priority": null,
        "special": false,
        "reviewedBy": {
                "mail": "89ui.com",
                "employeeName": "aalkk",
                "departmentName": "opopee",
                "region": "iiiii"
            },
        "description": null
    }
]

有效MySQL查询方案 #

1. 使用JSON_CONTAINS（推荐，直观易记） #

该函数可直接检查JSON数组/对象中是否包含指定的JSON片段，完美匹配需求：

SELECT * FROM taskmaster.tasks
WHERE 
  -- 匹配assignedTo数组中任意人员的mail
  JSON_CONTAINS(assigned_to, '{"mail": "xxxxxxx.com"}')
  -- 匹配reviewedBy对象的mail
  OR JSON_CONTAINS(reviewed_by, '{"mail": "xxxxxxx.com"}');

2. 使用JSON_SEARCH（备选，通过查找路径判断） #

通过查找指定mail值在JSON中的路径，若路径存在则说明匹配成功：

SELECT * FROM taskmaster.tasks
WHERE 
  -- 查找assignedTo数组中mail等于指定值的路径
  JSON_SEARCH(assigned_to, 'one', 'xxxxxxx.com', NULL, '$[*].mail') IS NOT NULL
  -- 直接提取reviewedBy的mail进行比较
  OR JSON_EXTRACT(reviewed_by, '$.mail') = 'xxxxxxx.com';

失效原因说明 #

• JSON_EXTRACT(assigned_to,"$.mail")：assigned_to是数组，此路径无法正确定位元素，返回NULL。
• JSON_EXTRACT(assigned_to,"$[*].*")：返回整个数组的所有字段集合（JSON数组），无法直接与字符串"xxxxxxx.com"比较。
• 指定索引的$[0].mail：仅能匹配数组第一个元素，无法覆盖所有元素。

Spring JPA简化实现 #

方法1：原生SQL查询（最直接） #

在Repository接口中使用@Query注解编写原生SQL，直接复用MySQL的JSON函数：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import java.util.List;

public interface TaskRepository extends CrudRepository<Task, Long> {

    @Query(value = "SELECT * FROM tasks " +
                   "WHERE JSON_CONTAINS(assigned_to, :mailJson) " +
                   "OR JSON_CONTAINS(reviewed_by, :mailJson)",
           nativeQuery = true)
    List<Task> findTasksByAssignedOrReviewedMail(String mailJson);
}

调用时，需将mail封装为JSON字符串（可借助Jackson工具类避免手动拼接）：

// 示例：用Jackson生成JSON参数
ObjectMapper objectMapper = new ObjectMapper();
String mailJson = objectMapper.writeValueAsString(Map.of("mail", "xxxxxxx.com"));
List<Task> tasks = taskRepository.findTasksByAssignedOrReviewedMail(mailJson);

方法2：封装工具类简化参数生成 #

单独封装一个JSON工具类，统一生成查询所需的JSON参数：

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.stereotype.Component;
import java.util.Map;

@Component
public class JsonQueryHelper {
    private final ObjectMapper objectMapper;

    public JsonQueryHelper(ObjectMapper objectMapper) {
        this.objectMapper = objectMapper;
    }

    public String generateMailQueryParam(String mail) throws JsonProcessingException {
        return objectMapper.writeValueAsString(Map.of("mail", mail));
    }
}

调用时直接注入工具类使用即可，无需手动拼接JSON字符串。

内容的提问来源于stack exchange，提问作者Abrar Md