MariaDB使用JSON_EXTRACT提取JSON数组值异常求助

问题场景 #

我是MariaDB新手，遇到JSON字段查询异常：

• brand表结构：

  name varchar(255) NOT NULL
  sold longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid('sold'))
  

• 表内数据：

  name	sold
  Test	[{"id":"0","name":"0","count":5000},{"id":"1","name":"1","count":5000}]
  Test2	[{"id":"0","name":"0","count":10000},{"id":"1","name":"1","count":10000}]
  Test3	[{"id":"0","name":"0","count":15000},{"id":"1","name":"1","count":35000}]
  Test4	[{"id":"0","name":"0","count":700},{"id":"1","name":"1","count":600}]

执行以下查询：

SELECT name, JSON_EXTRACT(sold, '$[*].count') as cnt FROM brands;

或

SELECT name, JSON_EXTRACT(sold, '$**.count') as cnt FROM brands;

返回结果仅部分行正确返回数组，其余行只返回第一个count值：

预期所有行都返回包含所有count值的数组：

问题处理结果 #

该问题属于MariaDB的已知bug，已提交JIRA工单，且已在对应GitHub提交记录中完成修复。

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