Extracting value from a JSON array in a MariaDB table column

Question

Would appreciate if you can help a mariaDB newbie with this.

I have a mariaDB database table called 'brand' with data as below.

Datatypes are VARCHAR and JSON.

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}]

When I execute the query:

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

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

I get the below output

name	cnt
Test	[5000, 5000]
Test2	10000
Test3	15000
Test4	700

I was expecting output as below

name	cnt
Test	[5000, 5000]
Test2	[10000, 10000]
Test3	[15000, 35000]
Test4	[700, 600]

Can you please help me correct the error?

1. I have searched the mariaDB documentation https://mariadb.com/kb/en/json_extract/ but not able to find a solution yet.
2. I also looked at this post: How do I extract values from a JSON array in MariaDB or MySQL? and I am implementing same SQL...

Thank you!

Update: 25 Jan

Ticket submitted to JIRA mariadb and fixed here