MariaDB 10.10.2中JSON_EXTRACT带WHERE与不带WHERE结果不一致问题
2026-8-7
问题描述
在MariaDB 10.10.2中执行以下查询：
select l.Id , json_length(l.Response, '$.Response.Errors') as nErrors, json_extract(l.Response, '$.Response.Errors[*].Reason') as reasons from logs l LIMIT 0, 5;
得到结果：
+----+---------+-----------------------+ | Id | nErrors | reasons | +----+---------+-----------------------+ | 1 | 1 | ["UsernameEmpty"] | | 2 | 1 | "UserAlreadyLoggedIn" | | 3 | 2 | "EmailEmpty" | | 4 | 2 | "EmailNotValid" | | 5 | 2 | "EmailNotValid" | +----+---------+-----------------------+
但执行带WHERE子句的查询：
select l.Id , json_length(l.Response, '$.Response.Errors') as nErrors, json_extract(l.Response, '$.Response.Errors[*].Reason') as reasons from logs l where id = 3;
却得到完整结果：
+----+---------+-----------------------------------+ | Id | nErrors | reasons | +----+---------+-----------------------------------+ | 3 | 2 | ["EmailEmpty", "EmailDoNotMatch"] | +----+---------+-----------------------------------+
仅修改了WHERE子句，为何第一个查询只返回单个错误原因，第二个能返回完整的原因列表？
补充：数据库前5行原始数据如下：
+----+---------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------------------------+----------------------------+--------+ | Id | IpAddress | ElapsedTime | Request | Response | IsArchived | Created | LastModified | UserId | +----+---------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------------------------+----------------------------+--------+ | 1 | 192.168.230.3 | 280 | {"Body":"{\n \u0022Email\u0022: \u0022demo1234@test.com\u0022,\n \u0022EmailConfirm\u0022: \u0022demo1234@test.com\u0022,\n \u0022Username\u0022: \u0022\u0022,\n \u0022Password\u0022: \u0012345678\u0022,\n \u0022PasswordConfirm\u0022: \u0012345678\u0022\n}","ByteBody":null,"ContentType":"application/json","Cookies":{},"Headers":{"Accept":"*/*","Connection":"close","Host":"192.168.230.1:5001","User-Agent":"Thunder Client (https://www.thunderclient.com)","Accept-Encoding":"gzip, deflate, br","Content-Type":"application/json","Content-Length":"188"},"Method":"POST","Path":"/api/users","Query":"","Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | {"Response":{"StatusCode":400,"ByteResponse":null,"ByteContentType":"","Errors":[{"Type":"Validation","Parameters":null,"Class":"","Function":"","Message":"","ProdMessage":"","Reason":"UsernameEmpty","StackTrace":"","UserId":-1,"Username":""}],"Debug":[],"ElapsedTime":0},"Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | 0 | 2022-12-24 17:14:46.355917 | 2022-12-24 17:14:46.355917 | -1 | | 2 | 192.168.230.1 | 13 | {"Body":"{\r\n \u0022$id\u0022: \u00221\u0022,\r\n \u0022Email\u0022: \u0022demo0964657430@test.com\u0022,\r\n \u0022EmailConfirm\u0022: \u0022demo0964657430@test.com\u0022,\r\n \u0022Username\u0022: \u0022demo0964657430\u0022,\r\n \u0022Password\u0022: \u0012345678\u0022,\r\n \u0022PasswordConfirm\u0022: \u0012345678\u0022\r\n}","ByteBody":null,"ContentType":"text/plain; charset=utf-8","Cookies":{},"Headers":{"Host":"192.168.230.1:5001","Authorization":"Bearer tqdsAR2I/WDzj\u002BRUt7XHSDMz9WQ2Z4Ehekw2ey0QiB/06Z9\u002B0pujjpxn\u002BAG5i1EojpZWfLPaiR1Ybi59Xg\u002BLreCRpI80Uoc0mhxYXw7SpFCsA2x/VH5LByKfM/DUEC84tGpZmRlZk7eBBNLKsWv5/ymLQi95xA2w7p4XYJM72es=","Content-Type":"text/plain; charset=utf-8","Content-Length":"203"},"Method":"POST","Path":"/api/users","Query":"","Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | {"Response":{"StatusCode":400,"ByteResponse":null,"ByteContentType":"","Errors":[{"Type":"Validation","Parameters":null,"Class":"","Function":"","Message":"","ProdMessage":"","Reason":"UserAlreadyLoggedIn","StackTrace":"","UserId":-1,"Username":""}],"Debug":[],"ElapsedTime":0},"Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | 0 | 2022-12-24 17:20:02.404424 | 2022-12-24 17:20:02.404424 | -1 | | 3 | 192.168.230.1 | 214 | {"Body":"{\r\n \u0022$id\u0022: \u00221\u0022,\r\n \u0022Email\u0022: \u0022\u0022,\r\n \u0022EmailConfirm\u0022: \u0022demo1488578354@test.com\u0022,\r\n \u0022Username\u0022: \u0022demo1488578354\u0022,\r\n \u0022Password\u0022: \u0012345678\u0022,\r\n \u0022PasswordConfirm\u0022: \u0012345678\u0022\r\n}","ByteBody":null,"ContentType":"text/plain; charset=utf-8","Cookies":{},"Headers":{"Host":"192.168.230.1:5001","Content-Type":"text/plain; charset=utf-8","Content-Length":"177"},"Method":"POST","Path":"/api/users","Query":"","Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | {"Response":{"StatusCode":400,"ByteResponse":null,"ByteContentType":"","Errors":[{"Type":"Validation","Parameters":null,"Class":"","Function":"","Message":"","ProdMessage":"","Reason":"EmailEmpty","StackTrace":"","UserId":-1,"Username":""},{"Type":"Validation","Parameters":null,"Class":"","Function":"","Message":"","ProdMessage":"","Reason":"EmailDoNotMatch","StackTrace":"","UserId":-1,"Username":""}],"Debug":[],"ElapsedTime":0},"Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | 0 | 2022-12-24 17:26:05.526448 | 2022-12-24 17:26:05.526448 | -1 | | 4 | 192.168.230.1 | 45 | {"Body":"{\r\n \u0022$id\u0022: \u00221\u0022,\r\n \u0022Email\u0022: \u0022aaa\u0022,\r\n \u0022EmailConfirm\u0022: \u0022demo1488578354@test.com\u0022,\r\n \u0022Username\u0022: \u0022demo1488578354\u0022,\r\n \u0022Password\u0022: \u0012345678\u0022,\r\n \u0022PasswordConfirm\u0022: \u0012345678\u0022\r\n}","ByteBody":null,"ContentType":"text/plain; charset=utf-8","Cookies":{},"Headers":{"Host":"192.168.230.1:5001","Content-Type":"text/plain; charset=utf-8","Content-Length":"180"},"Method":"POST","Path":"/api/users","Query":"","Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | {"Response":{"StatusCode":400,"ByteResponse":null,"ByteContentType":"","Errors":[{"Type":"Validation","Parameters":null,"Class":"","Function":"","Message":"","ProdMessage":"","Reason":"EmailNotValid","StackTrace":"","UserId":-1,"Username":""},{"Type":"Validation","Parameters":null,"Class":"","Function":"","Message":"","ProdMessage":"","Reason":"EmailDoNotMatch","StackTrace":"","UserId":-1,"Username":""}],"Debug":[],"ElapsedTime":0},"Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | 0 | 2022-12-24 17:26:05.626461 | 2022-12-24 17:26:05.626461 | -1 | | 5 | 192.168.230.1 | 7 | {"Body":"{\r\n \u0022$id\u0022: \u00221\u0022,\r\n \u0022Email\u0022: \u0022aaa@\u0022,\r\n \u0022EmailConfirm\u0022: \u0022demo1488578354@test.com\u0022,\r\n \u0022Username\u0022: \u0022demo1488578354\u0022,\r\n \u0022Password\u0022: \u0012345678\u0022,\r\n \u0022PasswordConfirm\u0022: \u0012345678\u0022\r\n}","ByteBody":null,"ContentType":"text/plain; charset=utf-8","Cookies":{},"Headers":{"Host":"192.168.230.1:5001","Content-Type":"text/plain; charset=utf-8","Content-Length":"181"},"Method":"POST","Path":"/api/users","Query":"","Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | {"Response":{"StatusCode":400,"ByteResponse":null,"ByteContentType":"","Errors":[{"Type":"Validation","Parameters":null,"Class":"","Function":"","Message":"","ProdMessage":"","Reason":"EmailNotValid","StackTrace":"","UserId":-1,"Username":""},{"Type":"Validation","Parameters":null,"Class":"","Function":"","Message":"","ProdMessage":"","Reason":"EmailDoNotMatch","StackTrace":"","UserId":-1,"Username":""}],"Debug":[],"ElapsedTime":0},"Id":0,"Created":"0001-01-01T00:00:00","LastModified":"0001-01-01T00:00:00","IsArchived":false} | 0 | 2022-12-24 17:26:05.642447 | 2022-12-24 17:26:05.642447 | -1 | +----+---------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------------------------+----------------------------+--------+
原因分析
这是MariaDB 10.10版本中存在的JSON路径表达式解析bug：当使用[*]通配符提取数组元素时，查询返回多条结果时，MariaDB会错误地只返回数组的第一个元素；但单条结果时，通配符解析逻辑正常，能完整返回数组内容。
具体来说，json_extract(l.Response, '$.Response.Errors[*].Reason')中的[*]用于匹配数组所有元素，但多结果行场景下，MariaDB没有正确处理这个通配符，导致仅提取每个Errors数组中的第一个Reason值。
解决方法
有两种可行的处理方式：
- 改用
json_table+json_arrayagg提取完整数组
通过json_table将JSON数组展开为行，再用json_arrayagg将同一行的Reason重新聚合为数组，这种方式不受查询结果行数影响：
select l.Id, json_length(l.Response, '$.Response.Errors') as nErrors, json_arrayagg(jt.reason) as reasons from logs l join json_table( l.Response, '$.Response.Errors[*]' columns(reason varchar(255) path '$.Reason') ) jt group by l.Id, nErrors limit 0,5;
- 升级MariaDB版本
该bug在MariaDB 10.11及以上版本中已被修复，升级后原查询即可正确返回完整的Reason数组。
内容的提问来源于stack exchange，提问作者Luca
相关产品推荐
相关产品推荐
