如何在MariaDB中正确筛选JSON列里receivedStatus为YES的数据？

在MariaDB的pb_doctors_requests表中，laboratory_requests字段存储着包含多个对象的JSON数组，每个对象包含receivedStatus属性。需要实现以下筛选逻辑：

• 筛选出JSON数组中存在receivedStatus="YES"对象的表行，或排除包含receivedStatus="NO"对象的表行
• 原SQL查询返回所有数据，无法正确过滤

JSON数组样例：

[
  {
    "itemCode": "LAB-BIOCHEM-0007",
    "serviceName": "Blood Gas Test",
    "qty": "1",
    "rate": "5000.0",
    "total": "5000.0",
    "classification": "",
    "department": "LABORATORY",
    "inputDate": "2023-02-09",
    "paymentStatus": "",
    "receivedStatus": "YES",
    "processed": "",
    "cmTotal": "0.0",
    "receivingUsername": "",
    "processingUsername": "",
    "approvalStatus": "",
    "specimen": "",
    "doctorsRequestingName": ""
  },
  {
    "itemCode": "LAB-SEROL-001",
    "serviceName": "Anc Profile",
    "qty": "1",
    "rate": "3000.0",
    "total": "3000.0",
    "classification": "",
    "department": "LABORATORY",
    "inputDate": "2023-02-09",
    "paymentStatus": "",
    "receivedStatus": "NO",
    "processed": "",
    "cmTotal": "0.0",
    "receivingUsername": "",
    "processingUsername": "",
    "approvalStatus": "",
    "specimen": "",
    "doctorsRequestingName": ""
  },
  {
    "itemCode": "LAB-PARAS-001",
    "serviceName": "B/S For Mps",
    "qty": "1",
    "rate": "200.0",
    "total": "200.0",
    "classification": "",
    "department": "LABORATORY",
    "inputDate": "2023-02-09",
    "paymentStatus": "",
    "receivedStatus": "YES",
    "processed": "",
    "cmTotal": "0.0",
    "receivingUsername": "",
    "processingUsername": "",
    "approvalStatus": "",
    "specimen": "",
    "doctorsRequestingName": ""
  },
  {
    "itemCode": "LAB-MICROB-012",
    "serviceName": "Hvs Wet Prep And Gram Stain ",
    "qty": "1",
    "rate": "500.0",
    "total": "500.0",
    "classification": "",
    "department": "LABORATORY",
    "inputDate": "2023-02-09",
    "paymentStatus": "",
    "receivedStatus": "NO",
    "processed": "",
    "cmTotal": "0.0",
    "receivingUsername": "",
    "processingUsername": "",
    "approvalStatus": "",
    "specimen": "",
    "doctorsRequestingName": ""
  }
]

尝试的SQL查询：

Select
  *
from (
  SELECT distinct
    patient_name,
    age,
    visit_no,
    sex,
    laboratory_requests,
    JSON_UNQUOTE(
      JSON_EXTRACT(
        t.laboratory_requests,
        CONCAT('$[', x.seq, '].department')
      )
    ) AS department,
    JSON_UNQUOTE(
      JSON_EXTRACT(
        t.laboratory_requests,
        CONCAT('$[', x.seq, '].receivedStatus')
      )
    ) AS receivedStatus
  FROM
    pb_doctors_requests t
    INNER JOIN ( 
      SELECT seq FROM seq_0_to_10
    ) AS x ON
    JSON_EXTRACT(
      t.laboratory_requests,
      CONCAT('$[', x.seq, ']')
    ) IS NOT NULL
) as s
where receivedStatus in ('NO');

原查询的核心问题：

1. DISTINCT会合并同一表行的多个JSON对象拆分结果，导致筛选逻辑失效
2. 子查询将JSON数组拆分为独立行，但外层筛选仅过滤拆分后的行，无法排除原表中包含多状态对象的行
3. 若表行的JSON数组同时存在YES和NO对象，原查询仍会返回该表行的拆分结果，最终数据不符合预期

根据不同需求场景，提供以下实现方式：

场景1：筛选JSON数组中**至少存在一个receivedStatus="YES"**的表行 #

使用JSON_SEARCH直接检查数组内是否存在匹配对象：

SELECT *
FROM pb_doctors_requests
WHERE JSON_SEARCH(laboratory_requests, 'one', 'YES', null, '$[*].receivedStatus') IS NOT NULL;

• 参数说明：
  • 'one'：仅查找第一个匹配项，提升查询效率
  • '$[*].receivedStatus'：遍历数组所有对象的receivedStatus属性

场景2：返回表行，且仅保留JSON数组中receivedStatus="YES"的对象 #

需MariaDB 10.6+版本，用JSON_TABLE拆分数组后筛选，再重新聚合为JSON：

SELECT
  t.patient_name,
  t.age,
  t.visit_no,
  t.sex,
  JSON_ARRAYAGG(j.obj) AS filtered_laboratory_requests
FROM pb_doctors_requests t
JOIN JSON_TABLE(
  t.laboratory_requests,
  '$[*]' COLUMNS (
    obj JSON PATH '$',
    receivedStatus VARCHAR(10) PATH '$.receivedStatus'
  )
) j
WHERE j.receivedStatus = 'YES'
GROUP BY t.patient_name, t.age, t.visit_no, t.sex;

• JSON_TABLE将JSON数组转为临时表，提取完整对象和状态字段
• JSON_ARRAYAGG将筛选后的对象重新组合为JSON数组
• 分组字段需替换为表的主键或唯一标识，避免数据丢失

场景3：排除JSON数组中**存在任何receivedStatus="NO"**的表行 #

检查数组内是否不存在NO状态的对象：

SELECT *
FROM pb_doctors_requests
WHERE JSON_SEARCH(laboratory_requests, 'one', 'NO', null, '$[*].receivedStatus') IS NULL;

内容的提问来源于stack exchange，提问作者MICHAEL NJUGUNA