mariadb 无法使用满足特定条件的SQL查询查询Maria Db JSON数据中的所有JSON值

gstyhher 于 2023-02-12 发布在其他

关注(0)|答案(1)|浏览(150)

我无法选择Maria Db中满足特定条件的JSON列中的所有值。数据库字段中的数据被组织为JSON数组中的多个项目。
例如，如何检索 * 所有gson值 *，其中 receivedStatus 为 YES？
下面是我的示例数据

[{"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":""}]

我试过：
从（中选择 *

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');

我正在尝试的截图：
enter image description here

mariadb

来源：https://stackoverflow.com/questions/75408126/unable-to-query-all-json-values-in-maria-db-json-data-using-sql-query-where-a-ce

1条答案

按热度按时间

ljsrvy3e1#

尝试以下操作，通过键编号获取特定元素的值：

select json_extract(laboratory_requests, '$[0].receivedStatus')
from pb_doctors_requests;

如果要删除报价，请使用JSON_UNQUOTE：

select JSON_UNQUOTE(json_extract(laboratory_requests, '$[0].receivedStatus'))
from pb_doctors_requests;

要获取所有数组元素的值，可以使用JSON_TABLE：

SELECT get_values.* 
FROM pb_doctors_requests, 
     JSON_TABLE(laboratory_requests, '$[*]' COLUMNS (
         department VARCHAR(40)  PATH '$.department',
         receivedStatus VARCHAR(40)  PATH '$.receivedStatus'
      )
) get_values;

这是mariadb 10.3的工作版本：

SELECT 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,
  JSON_LENGTH(t.laboratory_requests)
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

Demo for mysql 8/ mariadb 10.6 and higher
Demo for mariadb 10.3

赞(0）回复(0）举报 2023-02-12

我来回答

mariadb 无法使用满足特定条件的SQL查询查询Maria Db JSON数据中的所有JSON值

1条答案

相关问题

热门标签

最新问答