如何在MariaDB中按条件查询JSON数组内的所有值？

我需要从MariaDB的JSON数组类型字段中，筛选出所有receivedStatus值为YES的JSON对象。

示例数据 #

该字段存储的JSON数组示例如下：

[
    {"itemCode":"LAB-BLOOD-TRAN-0001","serviceName":"Blood Crosmatching","qty":"1","rate":"1500.0","total":"1500.0","classification":"","department":"LABORATORY","inputDate":"2023-02-09","paymentStatus":"NO","receivedStatus":"YES","processed":"","cmTotal":"0.0","receivingUsername":"","processingUsername":"","approvalStatus":"","specimen":"","doctorsRequestingName":""},
    {"itemCode":"LAB-BIOCHEM-0001","serviceName":"ALBUMIN","qty":"1","rate":"500.0","total":"500.0","classification":"","department":"LABORATORY","inputDate":"2023-02-09","paymentStatus":"NO","receivedStatus":"NO","processed":"","cmTotal":"0.0","receivingUsername":"","processingUsername":"","approvalStatus":"","specimen":"","doctorsRequestingName":""},
    {"itemCode":"LAB-IMMUN-001","serviceName":"17 Beta Estradiol ","qty":"1.0","rate":"4500.0","total":"4500.0","classification":"IMMUNOLOGY","department":"LABORATORY","inputDate":"2023-02-09 17:01:27","paymentStatus":"YES","receivedStatus":"","processed":"","cmTotal":"4500.0","receivingUsername":"","processingUsername":"","approvalStatus":"APPROVED","specimen":"Urea","doctorsRequestingName":""},
    {"itemCode":"LAB-IMMUN-004","serviceName":"Acetyl Receptor Antibodies ","qty":"1","rate":"12500.0","total":"12500.0","classification":"","department":"LABORATORY","inputDate":"2023-02-09","paymentStatus":"NO","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：使用JSON_TABLE（推荐，MariaDB 10.4及以上版本支持） #

JSON_TABLE可以直接将JSON数组转换为关系型表格，筛选逻辑更直观：

SELECT 
    t.patient_name,
    t.age,
    t.visit_no,
    t.sex,
    jt.*
FROM pb_doctors_requests t
JOIN JSON_TABLE(
    t.laboratory_requests,
    '$[*]' COLUMNS (
        itemCode VARCHAR(50) PATH '$.itemCode',
        serviceName VARCHAR(100) PATH '$.serviceName',
        department VARCHAR(50) PATH '$.department',
        receivedStatus VARCHAR(10) PATH '$.receivedStatus'
        -- 可根据需要添加其他字段
    )
) jt
WHERE jt.receivedStatus = 'YES';

方法2：改进原有SQL语句 #

如果你的MariaDB版本较低不支持JSON_TABLE，可以修改原有查询，调整筛选条件并优化逻辑：

SELECT 
    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
WHERE JSON_UNQUOTE(JSON_EXTRACT(t.laboratory_requests, CONCAT('$[', x.seq, '].receivedStatus'))) = 'YES';

说明：

• 方法1的JSON_TABLE将JSON数组展开为行，直接对展开后的字段筛选，代码更简洁易维护，性能也更优。
• 方法2保留了原有关联序列表的方式，直接在WHERE子句中筛选receivedStatus为YES的条目，无需嵌套子查询。

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